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

 BRAND ITEM COST DIM COST TYPE COST AMOUNT COST % 011 123 XX GENERAL 10 10 011 123 XY GENERAL 10 10 011 123 YY GENERAL 80 80 011 123 - STORAGE 120 0
1 ACCEPTED SOLUTION
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 %]]

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])

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

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

 BRAND ITEM SPREAD COST CALC A 1 10 20 100*10% A 1 90 100 100*90% B 2 80 322 200*80% B 2 20 50 200*20% C 3 100 44 300*100%
Resolver I

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

Helper I

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?

Resolver I

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:

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

Resolver I

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:

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.

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

This returns the answer within this dummy dataset.  I have no idea about performance though.

Hope this helps

Regards
Richard
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 %]]

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])

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

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])

Announcements

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

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

#### Business Application LATAM Summit 2023

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

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