Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kenneth0596
Frequent Visitor

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
5 REPLIES 5
v-henryk-mstf
Community Support
Community Support

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
Impactful Individual
Impactful Individual

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.

VahidDM
Super User
Super User

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:

 

VahidDM_0-1647901612071.png

 

 

 

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.