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

Distribute subtotal of specific type on other rows

Hello, i am quite new to DAX Commands and PowerPivot but i am trying to achieve the following and am quite sure that this should be possible quite easy.

At the moment i have a classical pivot table of multiple levels which has the following hierarchy

BRAND => ITEM => COST DIMENSION

What i want to achieve is to dynamically calculate a Subtotal of any existing BRAND<=>ITEM combination which i am then using as a multiplier in order to distribute this subtotal on any existing row based on their particular percentage share.

 

In the table below i would need a measure which saves the Cost for Type = Storage and then distributes this amount on the corresponding rows above based on their percentage share.

This has to be done also for the following brand<=>item combinations as this is just illustrating the first group of many.

 

Hope i made myself clear. If not, please feel free to ask! Thanks a lot!

BRANDITEMCOST DIMCOST TYPECOST AMOUNTCOST %
011123XXGENERAL1010
011123XYGENERAL1010
011123YYGENERAL8080
011123-STORAGE1200
      
1 ACCEPTED SOLUTION
Richard_100
Resolver I
Resolver I

Hello

 

I wasn't able to fully able to figure out your Brand/Item/Cost Dimension but my understanding is that this is a subtotal and apportion job.  My take below is a more general approach using a table I've built myself with just two levels for Brand and Product.  I've put in random values for Cost and I've made sure that the apportionment value Spread % adds up to 100% for each Brand/Product combination

 

In a basic Excel table the column "Goal" is achieved using a SUMIFS formula that adds the column based on the value of the row in that column:

 

SUMIFS([Cost], [Brand], [@Brand], [Product], [@Product])*[@[Spread %]]

 

Richard_100_0-1643883922979.png

 

In DAX, I achieved this in a calculated column using:

 

VAR _OuterBrand=Table1[Brand]
VAR _OuterProduct=Table1[Product]
VAR _Calc = SUMX(FILTER(Table1, Table1[Brand]=_OuterBrand && Table1[Product]=_OuterProduct), Table1[Cost])
RETURN _Calc * Table1[Spread %]

 

Richard_100_1-1643884096154.png

 

I know this isn't exactly your data, which has three levels, but I hope it helps you get to the answer for you

 

Regards

Richard

 

View solution in original post

7 REPLIES 7
padinator
Helper I
Helper I

Well, yes maybe i explained it a little bit confusive. Basically my problem is that for ANY BRAND&ITEM Combination i have a Storage Cost of "X". Now i need a way to dynamically store this Storage amount per BRAND<=>ITEM COMBO and multiply this amount in the corresponding rows times a spread which i already have.

 

So f.e. for

BRAND A ITEM 1 i would have Cost 100

BRAND B ITEM 2 i would have Cost 200

BRAND C ITEM 3 i would have Cost 300

 

I would want ONE SINGLE VARIABLE "STORAGECOST" which i am multiplying with the [Spread%] in any given row but while "STORAGECOST" will dynamically differ based on the row (and BRAND|ITEM) i am currently in. See example below ==> basically there are 2 sections (1 where i get the corresponding storage cost and 2 where i distribute this corresponding storage cost based on the spread of the particular row)

 

BRANDITEMSPREADCOSTCALC
A11020100*10%
A190100100*90%
B280322200*80%
B22050200*20%
C310044300*100%

Hello

 

I believe that's what my formula does; it calculates the subtotal of a cost column for a brand/product combination and multiplies it by a spread % column.  It does both steps within the same formula: in the RETURN block it has _Calc * Spread % where _Calc is that subtotal and the spread is taken from the line.

 

Sorry if I've misunderstood; it may be easier for myself and others if you create and share a PBIX file that has all the columns and data to work with

 

Regards

Richard

Well, this works but goes to disatvantage of performance unfortunately. In this way i do calculate the total Cost Amount in any single line while i would normally just need to calculate the total amount once per Item<=>Brand combination. So working yes, but not really practicable unfortunately. Is there not another solution where i can, as i suggested, calculate the sum per Combination one time and then use it dynamically at the corresponding places?

If you have a Brand/Product table separately (one line per combination, with a unqiue identifier column that has a relationship to the same column in the main fact table), you could put a calculated column there.  It would either be a slightly adapted version of what I had, or something like SUMX( RELATEDTABLE('Table1', [Cost]) ).  And then it would sit there, ready to be used. 

 

In your main table, which would have a relationship to this other table via the unique identifier, you could have a calculated column that would be along the lines of RELATED('ProductBrandTable'[Cost]) * [Spread %] ).

 

 

In your case, you have more than two things that define it as unique (Brand + Item + Cost Dimension, at least) but you could construct one specially, either with Power Query as a new query that summarises your main fact table or as a DAX defined table

 

For defining the table with DAX, I think this would do it:

 

ADDCOLUMNS(

            SUMMARIZE(Table1, [Brand],[Product]),

             "@Cost", CALCULATE(SUM(Table1[Cost]))

               )

 

However, I don't know how to make this into a table which can be constructed once and linked to the data model, to be accessed using a RELATED([@Cost]) type method as described above

 

I hope all the above is of some help.  It is more just me thinking out loud rather than a targeted solution for you.  It felt a bit circular to have a table that summarises the main table and is then referenced by that main table again, so I don't know how performant it would be.  I think you need one of the big guns on the board to swoop in and help you out to optimise further

 

Regards

Richard

Just to follow on, because I was curious, I have created my potential solution in a PBIX file which I don't know how to attach but hopefully there are enough screenshots here to help

 

Using the modelling features, I made a new table with this:

 

ADDCOLUMNS(

            SUMMARIZE('Table', 'Table'[Brand],'Table'[Product]),

             "@Cost", CALCULATE(SUM('Table'[Cost]))

               )

 

I have a unique identifier field in my main 'Table' and also added to the above, basically [Brand] & [ Product] in both.  I created a relationship from 'Table 2' to 'Table' on this identifier.

 

Richard_100_1-1643911602528.png

Richard_100_2-1643911654151.png

 

 

I then added a new calculated column in the original table:

 

RELATED('Table 2'[@Cost]) * 'Table'[Spread %]
 
This returns the answer within this dummy dataset.  I have no idea about performance though.
Richard_100_0-1643911276053.png

 

Hope this helps
 
Regards
Richard
Richard_100
Resolver I
Resolver I

Hello

 

I wasn't able to fully able to figure out your Brand/Item/Cost Dimension but my understanding is that this is a subtotal and apportion job.  My take below is a more general approach using a table I've built myself with just two levels for Brand and Product.  I've put in random values for Cost and I've made sure that the apportionment value Spread % adds up to 100% for each Brand/Product combination

 

In a basic Excel table the column "Goal" is achieved using a SUMIFS formula that adds the column based on the value of the row in that column:

 

SUMIFS([Cost], [Brand], [@Brand], [Product], [@Product])*[@[Spread %]]

 

Richard_100_0-1643883922979.png

 

In DAX, I achieved this in a calculated column using:

 

VAR _OuterBrand=Table1[Brand]
VAR _OuterProduct=Table1[Product]
VAR _Calc = SUMX(FILTER(Table1, Table1[Brand]=_OuterBrand && Table1[Product]=_OuterProduct), Table1[Cost])
RETURN _Calc * Table1[Spread %]

 

Richard_100_1-1643884096154.png

 

I know this isn't exactly your data, which has three levels, but I hope it helps you get to the answer for you

 

Regards

Richard

 

amitchandak
Super User
Super User

@padinator , You can get sub total like

 

Sub Total =

calculate(sum(Table[COST AMOUNT]), filter(allselected(Table), Table[Brand] = max(Table[Brand]) && Table[ITEM ] = max(Table[ITEM ])  ) )

 

 

I did not get the % logic

 

but % of subtotal is

divide(sum(Table[COST AMOUNT]), [Sub Total])

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors