Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am trying to do a calculation in matrix table. I'd like to calculate a rate (% of Gross) by summing up certain rows (NS, Contribution,OS) and divide by Gross to calculate % rate. Below is sample data and what i am trying to accomplish.
% of Gross | SUM(NS+Contribution+OSG)/Gross |
I tried numerous formulas but none of them work.
Calendar Year | Customer | PL Line | Values |
2019 | ACME Markets | Volume | 1,972,240 |
2019 | ACME Markets | Gross | 697,503 |
2019 | ACME Markets | Returns | - |
2019 | ACME Markets | Discounts | 3,398 |
2019 | ACME Markets | OSG | 206,648 |
2019 | ACME Markets | NS | 206,224 |
2019 | ACME Markets | Contribution | (109,594) |
Solved! Go to Solution.
@dokat , Try a new measure like
Divide(
calculate(sum(Table[Values]), filter(Table, Table[PL Line] in {"NS","Contribution","OSG"})),calculate(sum(Table[Values]), filter(Table, Table[PL Line] in {"Gross"})))
It worked. Thank you!
@dokat , Try a new measure like
Divide(
calculate(sum(Table[Values]), filter(Table, Table[PL Line] in {"NS","Contribution","OSG"})),calculate(sum(Table[Values]), filter(Table, Table[PL Line] in {"Gross"})))
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |