Forum Discussion
DAX CALCULATION
I'm trying to GM % on a row based. Net GP/Sales. Please can someone help me.
12 Replies
Anonymous , A measure like
divide(calculate([Measure], filter(Table, search("Net GP",Table[Line Description],,0) >0) ) ,
calculate([Measure], filter(Table, search("Sales",Table[Line Description],,0) >0) ) )
amitchandak 541,093.85/2,036,933,92 = Baked Good Gross Margin only
Anonymous , And you do not have Baked Good in another column, you want it to also be searched and created a GP?
My Idea would be to split this column from behind in power bi
first by (
then by space - Before doing the I will replace sales with net sales so I can split on 2nd space from last
If I am on the right path, I need a sample to work on.
amitchandak I'm looking to calculate Gross Margin on those Gross margin attribute only on ROW based both on Budget & Actuals. I don't want the GM % to be on Columns.
Anonymous , Not I am a bit confused.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.And share an example of calculation
Hi Anonymous ,
Please refer to my pbix file to see if it helps you.
Add an index column first.
Click Home>>Transform data >>Transform data>>Add column>>index column>>From 1.
Then create columns.
actual = VAR _aactual = CALCULATE ( MAX ( 'Table'[actual value] ), FILTER ( ALL ( 'Table' ), 'Table'[Index.1] = EARLIER ( 'Table'[Index.1] ) + 1 ) ) RETURN DIVIDE ( 'Table'[actual value], _aactual )budget = VAR _budget = CALCULATE ( MAX ( 'Table'[budget values] ), FILTER ( ALL ( 'Table' ), 'Table'[Index.1] = EARLIER ( 'Table'[Index.1] ) + 1 ) ) RETURN DIVIDE ( 'Table'[budget values], _budget )If I have misunderstood your meaning, please provide more details with your desired output and your pbix file without privacy information.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
