Forum Discussion

kenneth0596's avatar
kenneth0596
Frequent Visitor
4 years ago

Dax Calculate Filter

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. 

 

 

DepartmentCategoryFeeRevenuePercentage
A110010001.666666667
B150010001.666666667
C22008002
D21508002
E22508002
Need Help

5 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    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's avatar
      kenneth0596
      Frequent Visitor
      4 years ago

      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. 

  • YukiK's avatar
    YukiK
    Impactful Individual
    4 years ago
    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's avatar
      kenneth0596
      Frequent Visitor
      4 years ago

      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.

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    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