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! Hopefully somebody can help...
Is it possible to calculate a percentage total which changes dynamically based on other filters/slicers on the page, but is not affected by filtering the table to show the top 10 results?
For example, I have sales by date, business and category. For each of these I will have a slicer to allow a subset to be considered. I have calculated the %Total Sales, so that if I have all the data, its the percentage of all sales, but if I choose for example just one business, it´s the percentage of that business:
All data combined:
Date | Business | Category | Sales | Percent | Rank |
01/07/2018 | A | 1 | 100 | 11% | 4 |
01/07/2018 | A | 2 | 150 | 17% | 2 |
01/06/2018 | A | 1 | 125 | 14% | 3 |
01/07/2018 | B | 1 | 75 | 9% | 6 |
01/07/2018 | B | 2 | 25 | 3% | 7 |
01/07/2018 | C | 1 | 300 | 34% | 1 |
01/07/2018 | C | 2 | 100 | 11% | 4 |
Filtered for Business A:
Date | Business | Category | Sales | Percent | Rank |
01/07/2018 | A | 1 | 100 | 27% | 3 |
01/07/2018 | A | 2 | 150 | 40% | 1 |
01/06/2018 | A | 1 | 125 | 33% | 2 |
Now, I want to only show the top observation based on the variable percent. If I just say, filter for highest percentage, or for rank=1, it recalculates the percentage to be 100%. Can I change it to ignore only the top n filter, and still calculate the percentage based on all other active filters?
Many thanks in advance!
Sounds kind of like an ALLEXCEPT where you exclude Rank. But, I would probably try CALCULATETABLE where you change the filter for Rank to ALL('Table'[Rank]) (column version of ALL) which might remove the TOPN filter if it is based on Rank.
Thanks Greg.
I think ALLEXCEPT would work if Rank was a column, but I need it to be a measure so that it changes with the filters.
I have had a look at the CALCULATETABLE function but I'm afraid I don't follow what you mean, or understand exactly how to use the function. Again it seems to not like the fact that rank is a measure. Is that correct or have I misunderstood?
OK, if you post your Rank measure formula, I'll see if I can come up with a full solution.
Ok, so below is my measure for rank, and my measure for the percent_sales which is the measure I would like to change based on the other filters but not be affected by setting the table to show rank 1-10 only. (Equally, I could use the TopN filter on my BaseItems using top 10 based on Sales column if that is more straightforward, but that also doesn't seem to be working correctly with the ALLEXCEPT!)
Ranking= RANKX( ALL(report_excel[BaseItem]); calculate(SUM(report_excel[Sales] )))
percent_sales =
VAR Sales =
CALCULATE(SUM ( report_excel[Sales] ))
VAR AllSales =
CALCULATE( SUM ( report_excel[Sales] ); ALLselected('report_excel'))
RETURN
DIVIDE ( Sales; AllSales )
Thanks for your help!
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |