DAX CALCULATION

[Anonymous]

I'm trying to GM % on a row based. Net GP/Sales. Please can someone help me. 

[Anonymous]

Hi @Anonymous ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

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.

[Anonymous]

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.

[Anonymous]

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

[amitchandak]

@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

[Anonymous]

@amitchandak It won't let me share sample file. only Pix

[amitchandak]

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

[Anonymous]

@amitchandak  541,093.85/2,036,933,92 = Baked Good Gross Margin only 

[amitchandak]

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

[Anonymous]

The Baked goods are attribute on line Description. I need to Divide 
Baked goods Net GP/ Baked goods sales Using the Actuals & Budget meassures. So it will Populate Baked Goods gross margin row based. 

[Anonymous]

@amitchandak I'm trying to used SelectedValue & Switch function. This is how the actuals & Budget was Calculated. 

[amitchandak]

@Anonymous , what is the difference in the measure? for each switch value.  I think, need to look into details, what was needed and why the above was needed

[Anonymous]

@amitchandak  What was needed is the GM % from Acutal & Budget Values. Divide 541,093.85/2,036,933.92 = Baked Good Gross Margin. Every line descriptioin has a Gross Margin attribute Users wants fill in those gross margin attribute.