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.
Research
My issue is similar to the below ones, but I don't know how to handle filters in these
https://community.powerbi.com/t5/Desktop/Exclude-rows-from-sum-based-on-condition/td-p/1166556
Data & Workbook
My PowerBI file:
https://1drv.ms/u/s!AgLy4UR8UpdihuBwoVPT3d1ojzdOvw?e=9aLVX6
My real data is the same as this except I have 5 filters e.g Country, Gender, Age etc. instead of just Country
Problem
The "Percent of column total" in Matrix tables is super powerful. It calculates automatically for me the percentage of my sum over the selected dimension, and changes appropriately when I change filter combinations.
Line charts unfortunately don't have this option 😞
I have spend ~2 hours now searching the forums and SO, and the most promising solutions are above. They don't seem to update to sum to 100% though with the filters I have applied.
So it works when both Germany & UK are selected. However, filtering to say just UK, the values don't sum to 100% like the "percent of column total" does. I expect the solution is simple but I'm not finding this DAX code to be that intuitive as a newbie to the platform. Any help would be greatly appreciated!
Let me know if any problems accessing the powerbi file or understanding the problem. Thanks 🙂
Edit: Added DAX denominator below. Numerator is SUM(Sheet1[N])
N.denom =
var current_row_date = MIN(Sheet1[Date])
var current_question = Min(Sheet1[Question])
return
CALCULATE(
SUM(Sheet1[N]),
FILTER(
ALL(Sheet1),
Sheet1[Date] = current_row_date &&
Sheet1[Question] = current_question
)
)
Solved! Go to Solution.
Hi @jphelps13kantar ,
Try this
N(% by Date) 2 = DIVIDE(MAX(Sheet1[N]),CALCULATE(SUM(Sheet1[N]),ALLEXCEPT(Sheet1,Sheet1[Date],Sheet1[Question],Sheet1[Country])))
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @jphelps13kantar ,
Try this
N(% by Date) 2 = DIVIDE(MAX(Sheet1[N]),CALCULATE(SUM(Sheet1[N]),ALLEXCEPT(Sheet1,Sheet1[Date],Sheet1[Question],Sheet1[Country])))
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
It works when I replace
MAX(Sheet1[N])
with
SUM(Sheet1[N])
so thank you! 🙂
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 |
---|---|
112 | |
97 | |
85 | |
68 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |