cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MasterSonic
Helper III
Helper III

Divide columns by unique value in column per category

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




CodeGroupProcessingCalculated column distinc count of code per groupStatus
101Jeans12Ordered
101Jeans12Ordered
102Jeans12In Delivery
103Shirt34In Delivery
104Shirt34In Delivery
105Shirt34In Delivery
106Shirt34Sold
1 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:

vyueyunzhmsft_0-1669776615743.png

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

vyueyunzhmsft_1-1669776646772.png

(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:

vyueyunzhmsft_2-1669776702443.png

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

View solution in original post

5 REPLIES 5
v-yueyunzh-msft
Community Support
Community Support

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:

vyueyunzhmsft_0-1669346749990.png

(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 :

vyueyunzhmsft_1-1669346786784.png

 

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

 

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" 

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

IMG_20221129_210917.jpg

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

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:

vyueyunzhmsft_0-1669776615743.png

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

vyueyunzhmsft_1-1669776646772.png

(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:

vyueyunzhmsft_2-1669776702443.png

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors