Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi everyone,
I have a table with such a structure:
Country | City | Sales |
USA | NYC | 100 |
USA | LA | 150 |
USA | Chicago | 200 |
UK | London | 50 |
UK | Manchester | 100 |
France | Paris | 200 |
France | Nice | 300 |
I created a dashboard in which the user can choose the country as a page filter.
One of the components in this page is a Dial Gauge, in which I want to show the average sales of this country, and define the target values as 25th and 75th percentiles (sales in that range will be shown in green, above and below in red).
I created 2 measures and defined them as the gauge's target values:
25th percentile=PercentileX.EXC('Table',[Sales],0.25)
75th percentile=PercentileX.EXC('Table',[Sales],0.75)
This works fine, but I discovered that the average of all countries is always in the range of 25th-75th percentiles.
So what I want to do is calculate the average per country, and then redefine my measures to show the 25th/75th percentiles among the averages.
In the above example, it would be something like:
Country | Average sales |
USA | 150 |
UK | 75 |
France | 150 |
And then show percentiles of this table.
Any idea how to do that?
Thank you all in advance,
Aviram
Solved! Go to Solution.
@AviramWeiss Try:
25th percentile =
VAR __Table = GROUPBY('Table',[Country],"Average Sales",AVERAGEX(CURRENTGROUP(),[Sales])
VAR __Result = PERCENTILEX.EXC(__Table,[Average Sales],0.25)
RETURN
__Result
@AviramWeiss Try:
25th percentile =
VAR __Table = GROUPBY('Table',[Country],"Average Sales",AVERAGEX(CURRENTGROUP(),[Sales])
VAR __Result = PERCENTILEX.EXC(__Table,[Average Sales],0.25)
RETURN
__Result
Works like a charm. Thank you very much!
For proper documentation, I had to put the GroupBy in a Calculate clause, to add a RemoveFilter (otherwise it always gave the result of the specific chosen country).
Thanks again.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
69 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
127 | |
29 | |
27 | |
24 | |
22 |