Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |