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 two measures in a table containing numbers.
Measures A and B.
A | B |
5 | 2 |
8 | 5 |
10 | 0 |
0 | 5 |
I want to divide B with A and get percentages, like so:
A | B | Measure |
5 | 2 | 40 % |
8 | 5 | 63 % |
10 | 0 |
|
0 | 5 | 0 % |
But for the total line, I want the sum of B divided by sum of A, ONLY if column A has a number, like so:
| A | B | Measure |
| 5 | 2 | 40 % |
| 8 | 5 | 63 % |
| 10 | 0 |
|
| 0 | 5 | 0 % |
Total | 23 | 12 | 30 % |
The number 5 in the last B cell is not included in the total.
The calculation becomes (12-5) / 23 = 30 %
Solved! Go to Solution.
Hello @Preben
Measure = SUMX(FILTER(Sheet1,Sheet1[A]<>BLANK()|| Sheet1[A]<>0),Sheet1[B])/SUMX(Sheet1,Sheet1[A])
Best regards
Maggie
Hi @Anonymous
Measure = CALCULATE(SUM(Sheet1[B]),FILTER(Sheet1,Sheet1[A]<>BLANK()||Sheet1[A]<>0))/SUM(Sheet1[A])
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @Preben
Measure = SUMX(FILTER(Sheet1,Sheet1[A]<>BLANK()|| Sheet1[A]<>0),Sheet1[B])/SUMX(Sheet1,Sheet1[A])
Best regards
Maggie
@Anonymous , Use divide function
No. A simple divide gives the example I gave in the thread:
A | B | Measure |
5 | 2 | 40 % |
8 | 5 | 63 % |
10 | 0 |
|
0 | 5 | 0 % |
= 52 %
It should ignore the last B column because the A column has a 0.
This rule should only work on A, and not B.
The correct answer should give 30 %
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 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |