cancel
Showing results for
Did you mean:
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:

 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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV

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

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

3 REPLIES 3
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

Proud to be a Super User!

Super User IV

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

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

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

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

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

Proud to be a Super User!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

#### 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