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.
I have a geography hierarchy, say
City > Borough > Neighborhood
I have created a measure that works on any of the levels, say
Market Share = DIVIDE([Subscriptors], [Universe], BLANK())
and where both `[Subscriptors]` and `[Universe]` sum their parts based on the hierarchy relation.
Then I have classified the cities with a new grouping column, say `Cities[cluster]` .
And so I want to calculate something similar to:
Target Share = CALCULATE( MAX([Market Share]),
FILTER(Cities, [cluster] = MAX([cluster])))
That is to say, the maximum market share, amongst the cities with the same grouping.
I'm still missing two pieces of the puzzle:
i) Letting Power BI know that I want to consider the market shares at the city level.
ii) Only considering those cities that belong to that same cluster.
One approach that fixed item (i) is the one followin the Quick Measure formats as follows:
Target Share = MAXX( KEEPFILTERS( VALUES( Cities[each_city])),
CALCULATE([Market Share]))
However, I couldn't figure out where to include condition that the `Cities[cluster]` be the same.
I hope this makes sense, and thanks for checking it out.
Cheers from Mexico.
Solved! Go to Solution.
Thank you for your answer.
While the suggested formula didn't work, and I tried different combinations of the above to get to the following:
Target Share = MAXX(
ALLEXCEPT(Cities, Cities[cluster]),
[Market Share])
Thanks!
Since [Target Share] is a measure, you don't need to wrap it in CALCULATE in your [Target Share] MAXX measure. You also shouldn't need the KEEPFILTERS around the VALUES. In any case, here is a way to get the MAXX of the cities within a common Cluster.
MAXX Cluster = CALCULATE(MAXX(ALL(Cities[City]), [Market Share]), VALUES(Cities[Cluster]))
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you for your answer.
While the suggested formula didn't work, and I tried different combinations of the above to get to the following:
Target Share = MAXX(
ALLEXCEPT(Cities, Cities[cluster]),
[Market Share])
Thanks!
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 |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |