Distribute subtotal of specific type on other rows

[padinator]

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!

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

[padinator]

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%

[Richard_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

[padinator]

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?

[Richard_100]

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

[Richard_100]

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.

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.

Hope this helps

Regards

Richard

[Richard_100 · Accepted solution]

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])
RETURN _Calc * Table1[Spread %]

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]

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