Sum by subcategory +Category/count of subcategory

[Anonymous]

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: 

category	subcategory	Cost
Ardmore	Ardmore 24	$1,653,166
Bellcow	Bellcow 21	$1,582,508
Bellcow	Bellcow 50	$1,390,279
Bellcow	Substation	$359,641
Belle Isle Sta	Belle Isle Sta 22	$679,518
Belle Isle Sta	Belle Isle Sta 26	$1,029,278
Belle Isle Sta	Belle Isle Sta 28	$732,765
Belle Isle Sta	Substation	$741,009
Bixby	Bixby 22	$920,072
Bixby	Bixby 29	$1,780,361
Bixby	Substation	$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)

category	subcategory	Cost	Total Spend
Ardmore	Ardmore 24	$1,653,166	$1,653,166
Bellcow	Bellcow 21	$1,582,508	$1,762,329
Bellcow	Bellcow 50	$1,390,279	$1,730,038
Bellcow	Substation	$359,641	$359,641
Belle Isle Sta	Belle Isle Sta 22	$679,518	$926,521
Belle Isle Sta	Belle Isle Sta 26	$1,029,278	$1,335,969
Belle Isle Sta	Belle Isle Sta 28	$732,765	$1,326,219
Belle Isle Sta	Substation	$741,009	$741,009
Bixby	Bixby 22	$920,072	$1,516,092.50
Bixby	Bixby 29	$1,780,361	$1,780,361.00
Bixby	Substation	$1,192,041	$1,192,041

[Greg_Deckler]

@Anonymous - 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-Roll/td-p/411443

[amitchandak]

@Anonymous , 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

[parry2k · Accepted solution]

@Anonymous 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.⚡