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.
Hi,
I have a simple DAX question, see below table. Im trying to calculate the percentage by dividing Revenue by Fee by Category. For example, Revenue is 1000 for Category 1 and Fee is 600 (100 + 500) then percentage is 1.666. How do I write this in DAX? Thanks.
Department | Category | Fee | Revenue | Percentage |
A | 1 | 100 | 1000 | 1.666666667 |
B | 1 | 500 | 1000 | 1.666666667 |
C | 2 | 200 | 800 | 2 |
D | 2 | 150 | 800 | 2 |
E | 2 | 250 | 800 | 2 |
Hi @kenneth0596 ,
What specific fields do you mean by page filter? Also can you provide the expected results and test the data model? Looking forward to your reply.
Best Regards,
Henry
Your Measure =
VAR __Rev =
CALCULATE(
MAX(YourTable[Revenue]),
ALLEXCEPT(YourTable, YourTable[Category])
)
VAR __Fee =
CALCULATE(
SUM(YourTable[Fee]),
ALLEXCEPT(YourTable, YourTable[Category])
)
RETURN
DIVIDE(__Rev, __Fee)
Please give it a thumbs up if this helps!
Thanks Yukik, it did compute the percentage correctly but when I dropped it into the table, it returned multiple duplicate rows like department a, a, a, b, b, c, c.
I forgot to mention that I have a page filter.
Hi @kenneth0596
How did you calculate 2 as a Percentage for category 2?
If that 2 is wrong, please try this measure:
Percent% =
VAR _Rev =
MAX ( 'Table'[Revenue] )
VAR _Fee =
CALCULATE (
SUM ( 'Table'[Fee] ),
FILTER ( ALL ( 'Table' ), 'Table'[Category] = MAX ( 'Table'[Category] ) )
)
RETURN
_Rev / _Fee
output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Thank you for your response.
I tried your measures but the calculate filter did not group the fee by category. VAR_Fee is still giving me the same result as Fee.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |