Divide columns by unique value in column per category

[MasterSonic]

Hi there,
What I would like to get is column/measure where I could get following:

Status count per Code ( unique) but if Status = Sold, it will count values from column Processing.
Value: Ordered - count 1
Value: In Delivery - count 4
from Status column

+

Count unique values in column Processing per  Group
Value: Jeans - count 1
Value: Shirt - count 3
from column Processing

Code	Group	Processing	Calculated column distinc count of code per group	Status
101	Jeans	1	2	Ordered
101	Jeans	1	2	Ordered
102	Jeans	1	2	In Delivery
103	Shirt	3	4	In Delivery
104	Shirt	3	4	In Delivery
105	Shirt	3	4	In Delivery
106	Shirt	3	4	Sold

[v-yueyunzh-msft]

Hi , @MasterSonic 

Sorry , i do not fully understand your need. In my side , i calculate the "IF status =Sold , return distinctcount of the [Processing] value in the table .  else return the distinctcount of the [Code] group by the [Status] in the table".

Here are the steps in my test:

(1)This is my test data:

(2)We can create a measure:

Status count per Code = var _status = SELECTEDVALUE('Table'[Status])
var _processing =DISTINCT( SELECTCOLUMNS( ALLSELECTED('Table') ,"Processing" , [Processing]))
var _t=DISTINCT(SELECTCOLUMNS( FILTER( ALLSELECTED('Table') ,'Table'[Status]=_status) , "Code" , [Code]))
return
IF(_status ="Sold" , COUNTROWS(_processing) , COUNTROWS(_t))

(3)Then we put the fields we need on the visual and we will get this result :

For your need , if this method donot meet you need , can you give the end result you want as a table to us according to your sample data  ?

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[MasterSonic]

Hi Aniya,

You helped a lot with that.

You solution basically solve the problem.

Only things is how to create seperate value for that?

I will quote you here:

"IF status =Sold , return distinctcount of the [Processing] value in the table .  else return the distinctcount of the [Code] group by the [Status] in the table".

How to add new value let's call it "Processing"

alongside

"Ordered", "In delivery","Sold" 

[v-yueyunzh-msft]

Hi, @MasterSonic 

Sorry , i am not very fully understanding about the " add new value let's call it "Processing"".

Can you share the end result you want to need as a table according to your test data to us so that we can help you better.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[MasterSonic]

 hello, i would like to creata a pie chart like that

[v-yueyunzh-msft · Accepted solution]

Hi , @MasterSonic 

According to your description, you want to add a "Processing" in the table . Right?

Here are the steps you can refer to :
(1)We need to create a table like this:

(2)Then we need to check we can not create relationship between two tables:

(3)Then we create a measure like this:

Value = var _status = SELECTEDVALUE('Status Table'[Status])
var _processing =DISTINCT( SELECTCOLUMNS( ALLSELECTED('Table') ,"Processing" , [Processing]))
var _t=DISTINCT(SELECTCOLUMNS( FILTER( ALLSELECTED('Table') ,'Table'[Status]=_status) , "Code" , [Code]))
var _pro_value =SUMX( SUMMARIZE(ALLSELECTED('Table'),[Group],[Processing]) , [Processing])
return
IF(_status="Processing",_pro_value,IF(_status ="Sold" , COUNTROWS(_processing) , COUNTROWS(_t)))

(4)Then we put this field we need on the pir visual and we will meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly