Forum Discussion

somtom's avatar
somtom
New Member
4 years ago
Solved

Similar Excel Subtotal function in DAX with Calculated Column or another route?

Hi, I have a problem so I made a simple sample, if sy can solve this. I have three different product in my company in six city of two differenc country. I would like to measure the working cost o...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi somtom ,

     

    Please create a new table:

     

    Category = {">=70%","50%-70%","Under 50%"}

     

    Then create these measures:

     

    Full Cost per City = 
CALCULATE (
    SUM ( 'Sample'[Full Cost] ),
    FILTER ( ALLSELECTED ( 'Sample' ), 'Sample'[City] = MAX ( 'Sample'[City] ) )
)
    Full Working Cost per City =
CALCULATE (
    SUM ( 'Sample'[Working Cost] ),
    FILTER ( ALLSELECTED ( 'Sample' ), 'Sample'[City] = MAX ( 'Sample'[City] ) )
)
    Working per Full = [Full Working Cost per City] / [Full Cost per City]
    Ratio Category =
IF (
    [Working per Full] >= 0.7,
    ">=70%",
    IF ( [Working per Full] >= 0.5, "50%-70%", "Under 50%" )
)
    CountCity =
CALCULATE (
    DISTINCTCOUNT ( 'Sample'[City] ),
    FILTER ( VALUES ( 'Sample'[City] ), [Ratio Category] = MAX ( Category[Value] ) )
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

v-kkf-msft's avatar
v-kkf-msft
Community Support
4 years ago

Hi somtom ,

 

Please create a new table:

 

Category = {">=70%","50%-70%","Under 50%"}

 

Then create these measures:

 

Full Cost per City = 
CALCULATE (
    SUM ( 'Sample'[Full Cost] ),
    FILTER ( ALLSELECTED ( 'Sample' ), 'Sample'[City] = MAX ( 'Sample'[City] ) )
)
Full Working Cost per City =
CALCULATE (
    SUM ( 'Sample'[Working Cost] ),
    FILTER ( ALLSELECTED ( 'Sample' ), 'Sample'[City] = MAX ( 'Sample'[City] ) )
)
Working per Full = [Full Working Cost per City] / [Full Cost per City]
Ratio Category =
IF (
    [Working per Full] >= 0.7,
    ">=70%",
    IF ( [Working per Full] >= 0.5, "50%-70%", "Under 50%" )
)
CountCity =
CALCULATE (
    DISTINCTCOUNT ( 'Sample'[City] ),
    FILTER ( VALUES ( 'Sample'[City] ), [Ratio Category] = MAX ( Category[Value] ) )
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.