Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I'm new to PoweBI and I'm looking for a way to divide the elements of a column by the total of another column while keeping filters on.
So for example:
A | B | date |
12 | 87 | 9.16 |
16 | 32 | 9.16 |
56 | 35 | 9.17 |
What I want is the elements of A in date 9.16 divide by the sum of B in date 9.16, so:
A/B | date |
12/(87+32) | 9.16 |
16/(87+32) | 9.16 |
I tried doing this in PowerBI
% AB= DIVIDE(SUM(table[A]),SUM(table[B]))
But it would give me
A/B |
12/87 |
16/32 |
Which is not desired.
Futhermore I need to keep the date filter in, so I don't think ALL() would be approporiate.
Any kind of help would be highly appreciated!
Solved! Go to Solution.
Hi @ningty09 ,
I created a measure to plement it with the function of ALLSELECTED. The table still can be filtered by date.
Divide = DIVIDE(SUM('Table'[A]),CALCULATE(SUM('Table'[B]),FILTER(ALLSELECTED('Table'),'Table'[date] = MAX('Table'[date]))))
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please refer
https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390
Hi, thanks for your reply. However, I'm trying to use the measure in different visuals and there will be different filter based on each individual visual so I don't think this solution applies.
Hi @ningty09 ,
I created a measure to plement it with the function of ALLSELECTED. The table still can be filtered by date.
Divide = DIVIDE(SUM('Table'[A]),CALCULATE(SUM('Table'[B]),FILTER(ALLSELECTED('Table'),'Table'[date] = MAX('Table'[date]))))
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |