Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Hi,
Try this measure
=IF(HASONEVALUE(Table1[Product]),[Price effect],SUMX(SUMMARIZE(VALUES(Table1[Product]),Table1[Product],"ABCD",[Price effect]),[ABCD]))
Hope this helps.
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |