Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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% |
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.
I then added a new calculated column in the original table:
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
@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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |