Price effect calculation in BI

[lingvistt]

Hi everyone,

I have the following data structure:

I want to calculate total 'Price effect', which is calculated by the following 2 step logic:

1. Calculate 'Price Effect' in absolute by each product. The formula: Price Effect = [(Sales per kg 2019)-(Sales per kg 2018)] x (Volume 2019).

2. Get total 'Price Effect' (Price effect A + Price effect B)

What I've done in BI:

1. Created measure 'Sales per kg'. The formula: Sales per Kg = SUM(Table1[Gross Sales])/SUM(Table1[Volume])

2. Created measure 'Price effect'. The formula: Price effect = (CALCULATE([Sales per Kg]; Table1[Time]="2019")-CALCULATE([Sales per Kg]; Table1[Time]="2018"))*CALCULATE(SUM(Table1[Volume]); Table1[Time]="2019")

As a result I see 'Price effect', which is correctly calculated if we look at result of each product. But for total it shows 89 M while I need to see total of 72 M (Price effect A + Price effect B).

Could you please advise me what measure shall I create to achieve the needed result? Thank you.

[Ashish_Mathur]

Hi,

Try this measure

=IF(HASONEVALUE(Table1[Product]),[Price effect],SUMX(SUMMARIZE(VALUES(Table1[Product]),Table1[Product],"ABCD",[Price effect]),[ABCD]))

Hope this helps.

[v-gizhi-msft · Accepted solution]

Hi,

Please try this measure:

Price effect = 
SUMX (
    DISTINCT ( 'Table1'[Product] ),
    (
        CALCULATE ( [Sales per Kg], Table1[Time] = "2019" )
            - CALCULATE ( [Sales per Kg], Table1[Time] = "2018" )
    )
        * CALCULATE ( SUM ( Table1[Volume] ), Table1[Time] = "2019" )
)

And it shows:

Hope this helps.

Best Regards,

Giotto Zhi

[amitchandak]

Join with date table using the date and then time intelligence

sales per Kg = divide(sum(Table1[Gross Sales]),SUM(Table1[Volume]))

	YTD sales per Kg = CALCULATE([sales per Kg],DATESYTD(('Date'[Date]),"12/31"))
	YTD Volume = CALCULATE(sum(Table1[Volume]),DATESYTD(('Date'[Date]),"12/31"))
	Last YTD sales per Kg = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

	Price Effect = divide(([YTD sales per Kg]-[Last YTD sales per Kg ]),[YTD Volume])
	

If you only have year. move that to year table or change the code below

sales per Kg = divide(sum(Table1[Gross Sales]),SUM(Table1[Volume]))

	YTD sales per Kg = 
	var _year =max(allselected(year),year[Year])
	return
	CALCULATE([sales per Kg], all(year),table[year]=_year)
	
	YTD Volume = 
	var _year =max(allselected(year),year[Year])
	return
	 CALCULATE(sum(Table1[Volume]), all(year),table[year]=_year)
	 
	Last YTD sales per Kg = 
	var _year =max(allselected(year),year[Year])-1
	return
	CALCULATE(SUM(Sales[Sales Amount]),all(year),table[year]=_year)

	Price Effect = divide(([YTD sales per Kg]-[Last YTD sales per Kg ]),[YTD Volume])

You can hard code year value if required in var _year

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[lingvistt]

Thank you for your reply! But this is not what I expected.

The problem is not in time intelligence. The formulas you propose are very similar to those, which I already use, and they work perfectly.

The problem is that as a total price effect it shows 89 M, but I need 72 M (sum of absolute price effect A and price effect B). Below is the screenshot with the current result and expectations.