Forum Discussion
Counting a measure return
Hello,
I'm linking to a previous post of mine to help give more context if needed.
https://community.powerbi.com/t5/Desktop/Measures-affected-by-slicer-choice/m-p/595019#M282535
I have a follow up question. I am trying to determine how to create a measure that will count the returns from another measure. For example:
I have the following data, in which I determined how to find the minimum price per item. The data I'm using has a similar structure to this:
ITEM ID SELLER PRICE
1 A $50
1 B $40
1 C $45
2 A $100
2 C $120
3 A $10
3 B $11
3 C $9
I use the following measure to return the minimum price seller for each item based upon sellers selected in a slicer.
MinPriceSeller = VAR minprice = CALCULATE ( MIN ( 'Table'[PRICE] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[ITEM ID] = SELECTEDVALUE ( 'Table'[ITEM ID] ) ) ) RETURN CALCULATE ( SELECTEDVALUE ( 'Table'[SELLER] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[PRICE] = minprice ) )
So my return is technically a string value.
The output (with all sellers selected in a slicer), when put into a table visual, looks like the following:
ITEM ID MinPriceSeller PRICE
1 B $40
2 A $100
3 C $9
The output (with only sellers A and C selected in a slicer), when put into a table visual, looks like the following:
ITEM ID MinPriceSeller PRICE
1 C $45
2 A $100
3 C $9
What I am attempting to do now is creature another measure that will count the IDs that correspond to each "MinPriceSeller", returning the count, and then be able to visualize this in a clustered column chart. Aka, I want this measure to show MinPriceSeller has 2 Cs and 1 A in the previous example. This chart would have the MinPriceSeller on the X-axis and the count of the IDs on the Y-axis. I can't seem to find a way to return the correct count.
Thank you, and let me know if you need more information!
Hi Anonymous,
Please try a solution like below.
1. Create a new table [Sellers].
Sellers = VALUES('Table'[SELLER])2. Create a measure.
Measure = SUMX ( ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Table'[ITEM ID], 'Table'[SELLER], 'Table'[PRICE] ), "ifMinPrice", IF ( [PRICE] = CALCULATE ( MIN ( 'Table'[PRICE] ), ALL ( 'Table'[SELLER], 'Table'[PRICE] ), ALLSELECTED ( 'Sellers'[SELLER] ) ), 1, 0 ) ), [ifMinPrice] )
Best Regards,
11 Replies
Hi Anonymous,
Please try a solution like below.
1. Create a new table [Sellers].
Sellers = VALUES('Table'[SELLER])2. Create a measure.
Measure = SUMX ( ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Table'[ITEM ID], 'Table'[SELLER], 'Table'[PRICE] ), "ifMinPrice", IF ( [PRICE] = CALCULATE ( MIN ( 'Table'[PRICE] ), ALL ( 'Table'[SELLER], 'Table'[PRICE] ), ALLSELECTED ( 'Sellers'[SELLER] ) ), 1, 0 ) ), [ifMinPrice] )
Best Regards,
Hello v-jiascu-msft
Thanks for the response.
I applied this to my data, and it works when I do not filter any sellers out in my slicer.
When something is selected in my slicer, it only filters to show the value from "all selected", not what the results would be when including all the selected sellers to find the minimum price seller.
The original table where the lowest price seller is showing the correct data, but not the visual I created from the most recent measure.
Let me know if that's not clear. Thanks!
Hi Anonymous,
Do you have an independent table [Sellers] that the slicer is from? If you have done all the steps in my last post, please share a more accurate sample. Please mask the sensitive parts first.
Best Regards,