Dax Calculate Filter

[kenneth0596]

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

[v-henryk-mstf]

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

[YukiK]

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!

[kenneth0596]

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.

[VahidDM]

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/

[kenneth0596]

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.