Forum Discussion

JanD's avatar
JanD
Frequent Visitor
4 years ago
Solved

Price analysis / complex DAX

Hey there!   I have a bit of challenge here. Working on price anaysis of a portfolio. I need to define what is the price increase of my portfolio for all products appearing in both time periods A...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    All measures are in the attached pbix file.

     

     

     

JanD's avatar
JanD
Frequent Visitor
4 years ago

Hi Jihwan_Kim, 

 

It is exactly what I needed. Thanks a lot!

Basically I managed to get the same result, but in much complicated way, plus using bigger database I started having performance issues.

 

My solution looked like this:

 

Increase base old help =
IF ( [SalesAY] && [SalesLY] > 0, [QuantityLY], 0 )

 

Increase base old =
CALCULATE (
    [QuantityLY],
    FILTER ( VALUES ( data[Product] ), [SalesAY] && [SalesLY] > 0 )
)

 

Price delta old =
( [SalesAY] / [QuantityAY] - [SalesLY] / [QuantityLY] ) / ( [SalesLY] / [QuantityLY] )

 

Proportional increase old help =
IFERROR (
[Price delta old] * [Increase base help Old]
/ CALCULATE ( [Increase base old], ALLSELECTED ( data[Product] ) ),
0
)
 
Proportional increase old =
SUMX ( VALUES ( data[Product] ), [Proportional increase help old] )

 

 

I had to use 5 steps instead of your 3. I was fighting with summarizing my values in a proper, correct way. Additionally looks like formula Values makes the calculation much slower.

Nevertheless your solution really helped. (y)

Can you recommend a source for learning DAX? I have gone through all related to DAX on microsoft websites, but still couldn't built such an efficient calculation as you did. 🙂

 

 

Thanks
Jan

 

 

Jihwan_Kim's avatar
Jihwan_Kim
Super User
4 years ago

Hi, 

Thank you for your feedback.

Many people say reading the book, "The Definitive Guide to DAX", may let you level up in developing Power BI reports. I also agree with them. It is not a beginner-level-book but it will surely bring your skills up to the next level.

Thanks.