cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Price effect calculation in BI

Hi everyone,

I have the following data structure:

Table.PNG

 

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

Table2.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Price effect calculation in BI

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:

1.PNG

Hope this helps.

 

Best Regards,

Giotto Zhi

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Price effect calculation in BI

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Price effect calculation in BI

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.

Table3.PNG

Highlighted
Community Support
Community Support

Re: Price effect calculation in BI

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:

1.PNG

Hope this helps.

 

Best Regards,

Giotto Zhi

View solution in original post

Highlighted
Super User III
Super User III

Re: Price effect calculation in BI

Hi,

Try this measure

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

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors