cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Sum by subcategory +Category/count of subcategory

Hello All, 

I have a situation where i have to calculate the sum by subcategory + (sum of category / count of subcategory)

in the table Substation represent the cost for the Category

Table: 

categorysubcategoryCost 
ArdmoreArdmore 24$1,653,166
BellcowBellcow 21$1,582,508
BellcowBellcow 50$1,390,279
BellcowSubstation$359,641
Belle Isle StaBelle Isle Sta 22$679,518
Belle Isle StaBelle Isle Sta 26$1,029,278
Belle Isle StaBelle Isle Sta 28$732,765
Belle Isle StaSubstation$741,009
BixbyBixby 22$920,072
BixbyBixby 29$1,780,361
BixbySubstation$1,192,041

Calcination we needed 

example: for Subcategory Ardmore 24 there are no category so 

Total Spend = $1,653,166

 

For Bellcow Category we have 2 subcategory’s so the Total Spend needs to be

now lets take Subcategory Bellcow 21 we a category Bellcow as substation so we need 

Total Spend = Subcategory+Category/Count of Subcategory      ($1,582,508+($359,641/2) = $1,762,329)

 

now lets take Subcategory Bellcow 50 we a category Bellcow as substation so we need 

Total Spend = Subcategory+Category/Count of Subcategory       ($1,390,279+($359,641/2) = $1,730,038)

 

Now for Belle Isle Sta Category we have 3 subcategory’s so the Total Spend needs to be

now lets take Subcategory Belle Isle Sta 22 we a category Bellcow as substation so we need 

Total Spend = Subcategory+Category/Count of Subcategory       ($679,518+($741,009/3) = $926,521)

 

 
categorysubcategoryCost Total Spend
ArdmoreArdmore 24$1,653,166$1,653,166
BellcowBellcow 21$1,582,508$1,762,329
BellcowBellcow 50$1,390,279$1,730,038
BellcowSubstation$359,641$359,641
Belle Isle StaBelle Isle Sta 22$679,518$926,521
Belle Isle StaBelle Isle Sta 26$1,029,278$1,335,969
Belle Isle StaBelle Isle Sta 28$732,765$1,326,219
Belle Isle StaSubstation$741,009$741,009
BixbyBixby 22$920,072$1,516,092.50
BixbyBixby 29$1,780,361$1,780,361.00
BixbySubstation$1,192,041$1,192,041

 

1 ACCEPTED SOLUTION

Accepted Solutions

@rasala583 try this measure

 

Measure 2 =
VAR __count =
    CALCULATE (
        COUNTROWS ( 'Table (2)' ),
        ALLEXCEPT ( 'Table (2)', 'Table (2)'[category] ),
        'Table (2)'[subcategory] <> "Substation"
    )
VAR __substation =
    CALCULATE (
        SUM ( 'Table (2)'[Cost ] ),
        ALLEXCEPT ( 'Table (2)', 'Table (2)'[category] ),
        KEEPFILTERS ( 'Table (2)'[subcategory] = "Substation" )
    )
RETURN
    //DIVIDE ( 
    SUM ( 'Table (2)'[Cost ] )
        + DIVIDE (
            __substation,
            IF ( MAX ( 'Table (2)'[subcategory] ) = "Substation", 0, __count )
        )

 

You can tweak it as per your need but it will get you started.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

3 REPLIES 3
Super User IV
Super User IV

@rasala583 , no very clear. But seems like you need use allexcept

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@rasala583 try this measure

 

Measure 2 =
VAR __count =
    CALCULATE (
        COUNTROWS ( 'Table (2)' ),
        ALLEXCEPT ( 'Table (2)', 'Table (2)'[category] ),
        'Table (2)'[subcategory] <> "Substation"
    )
VAR __substation =
    CALCULATE (
        SUM ( 'Table (2)'[Cost ] ),
        ALLEXCEPT ( 'Table (2)', 'Table (2)'[category] ),
        KEEPFILTERS ( 'Table (2)'[subcategory] = "Substation" )
    )
RETURN
    //DIVIDE ( 
    SUM ( 'Table (2)'[Cost ] )
        + DIVIDE (
            __substation,
            IF ( MAX ( 'Table (2)'[subcategory] ) = "Substation", 0, __count )
        )

 

You can tweak it as per your need but it will get you started.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Super User IV
Super User IV

@rasala583 - Not entirely clear here but seems like this might help:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors