Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors