Several calculation in one measure

olondo · ‎05-21-2019

Dear PowerBI users,

I'm struggling with the COA structure of the P&L.

https://ibb.co/yd98zBL

In the image, you will find the beginning of the COA.

The first column is Description with the name of the Main Account.

Second column is the ActualYTD. The figures are correctly.

In the third column, I tried to get the gross margin, but it is not working as you can see.

The gross margin would be:

(Standard Cost of Goods Sold + Gross Sales 3rd Party) / Gross Sales 3rd Party

The result of this measure would be 0,43 (43%).

I thought, if I add a conditional column with 0, 1 and 2 I can get the measure to turn around the calculation in some rows.

In the third column (test), the measure would be:

test = IF(

[PercentageFormulaM] = 0 ; [ActualYTD] ;

IF([PercentageFormulaM] = 2 ; [Gross margin %] ; blank()))

But, if you can see, it is not the result what I expected. If you look at the fourth column, you will see that the Gross margin % measure (the measure IF([PercentageFormulaM] = 2 THEN Gross margin %) doesn't calculate it correctly. It gives me figures at the row 'Gross Sales 3rd Party' and 'Standard Cost of Goods Sold' (just the two rows which indicates the measure Gross margin % (COGS + Gross Sales)/Gross Sales). But it needs to calculate the measure in row 'Gross margin' instead of the two other rows.

Does anyone know what I'm missing in the measure? It would be a great help if we figure the correctly result out of this matter:)

If you need any further information, do not hesitate to place a comment below.

Tim

v-yuta-msft · ‎05-22-2019

@olondo ,

So in the measure of gross margin, 'Standard Cost of Goods Sold', 'Gross Sales 3rd Party' and 'Gross Sales 3rd Party' are three additional measures, right? This issue should be related to these additional measures you haven't provided, could you please share these measures?

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

olondo · ‎05-22-2019

@v-yuta-msft

Of course. The following measures are used in the P&L structure:

ActualYTD =

VAR YTD0 =

FILTER (

ALL ( 'Date' );

'Date'[Calendar YQMD Year] = MAX ( 'Date'[Calendar YQMD Year] )

&& 'Date'[DateValue] <= MAX ( 'Date'[DateValue] )

)

RETURN

CALCULATE ( [Actual]; YTD0 )

Gross margin % = ([Gross Sales 3rd Party YTD]+[Standard Cost of Goods Sold YTD])/[Gross Sales 3rd Party YTD]

Gross Sales 3rd Party YTD = CALCULATE([ActualYTD];FILTER('Verdichting COA';'Verdichting COA'[Description] = "Gross Sales 3rd Party"))

Standard Cost of Goods Sold YTD = CALCULATE([ActualYTD];FILTER('Verdichting COA';'Verdichting COA'[Description] = "Standard Cost of Goods Sold"))

test =

IF(

[PercentageFormulaM] = 0 ; [ActualYTD] ;

IF([PercentageFormulaM] = 2 ; [Gross margin %] ; blank()))

PercentageFormulaM = SUM('Verdichting COA'[Percentageformula])

If you look at the following link, it would explain the SUM(Percentageformula). I have filtered the table at the topics which is shown in the previous picture of the P&L structure.

https://ibb.co/Tmv0L5p

If you have more questions, do not hesitate to contact me.

Kind regards,

Tim