Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a dataset with a column called CityID and a measure called % of total sales MEASURE. The visual looks like this:
I'm trying to create two measures (and having zero success) showing the top selling city (1) and top percentage (29%).
Very grateful to anyone who can help with this?
Cheers,
Mark
Solved! Go to Solution.
Hi Mark,
You can use measures like this:
Top Selling City = FIRSTNONBLANK ( TOPN ( 1, VALUES ( YourTable[CityID] ), [% of total sales MEASURE] ), 1 )
Top Percentage = MAXX ( VALUES ( YourTable[CityID] ), [% of total sales MEASURE] )
(see this article for the first measure)
The above measures are subject to any filters on CityID. You could replace VALUES with ALL or ALLSELECTED in order to give a sensible result within a visual that includes the CityID dimension.
Regards,
Owen
Hi Mark,
You can use measures like this:
Top Selling City = FIRSTNONBLANK ( TOPN ( 1, VALUES ( YourTable[CityID] ), [% of total sales MEASURE] ), 1 )
Top Percentage = MAXX ( VALUES ( YourTable[CityID] ), [% of total sales MEASURE] )
(see this article for the first measure)
The above measures are subject to any filters on CityID. You could replace VALUES with ALL or ALLSELECTED in order to give a sensible result within a visual that includes the CityID dimension.
Regards,
Owen
Hey Owen,
Thanks so much, that's exactly what I needed.
Cheers,
Mark
Hi Owen,
Hope you're doing OK.
A couple of questions regarding the solution offered:
Apologies for butting in on the thread - I'm trying to build up my knowledge of DAX, hence the questions...
Cheers,
Matty
Hi Matty,
I'm fine thanks - hope you're well 🙂
Top Selling City = MINX ( TOPN ( 1, VALUES ( YourTable[CityID] ), [% of total sales MEASURE] ), YourTable[CityID] )
Top Selling City = CONCATENATEX ( TOPN ( 1, VALUES ( YourTable[CityID] ), [% of total sales MEASURE] ), YourTable[CityID], ", ", YourTable[CityID] )
Cheers
Owen
Thanks Owen. Explanation very clear.
Cheers,
Matty
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |