cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Price effect calculation in BI

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

## Re: Price effect calculation in BI

Hi,

``````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

4 REPLIES 4
Highlighted
Super User IX

## 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://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

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.

Highlighted
Community Support

## Re: Price effect calculation in BI

Hi,

``````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

Highlighted
Super User V

## 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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021