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 of full cost. I have a table with data and my aim is the other picture with that diagram to see the results in 3 category of ratios (above 70, 50-70, under 50 percent). And I want to use slicers with country and with the products too.
To solve this problem I wrote some calculated columns to the raw table in PBI data view:
Full Cost per City = CALCULATE(sum('Sample'[Full Cost]),FILTER('Sample','Sample'[City]=EARLIER('Sample'[City])))
Full Working Cost per City = CALCULATE(sum('Sample'[Working Cost]),FILTER('Sample','Sample'[City]=EARLIER('Sample'[City])))
Working per Full = 'Sample'[Full Working Cost per City]/'Sample'[Full Cost per City]
Ratio Category = if('Sample'[Working per Full]>=0.7,">=70%",if('Sample'[Working per Full]>=0.5,"50%-70%","Under 50%"))
And I wrote a simple measure for the chart: Number of Cities = distinctcount('Sample'[City])
It is not working solve because of Products slicing. I would need some similar as subtotal funciton in Excel, once when i filter the datas for Product 1, the cost of different cities would change and the categories too of course.
How can fix my solving route to aim it, or How can I approache this problem in another route?


 

 

 

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.

     

3 Replies

  • 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.

     

  • somtom's avatar
    somtom
    New Member
    4 years ago

    I accepted your solution. I'm very thankful. I thougt just it. I applied the solution route in my real, more-more comlex issue, and it is working well 🙂