Help - Creating price & quantity effect with DAX

[Anonymous]

Hi everyone!

I have an issue building measures to price and qty effect in a controling dashboard. I want to compare two different revisions of my cost plan (Plan_Version).

As you can see in the example above the Price_Effect formula is [(2020's tariff - 2019's tariff) * 2019's qty]  and Qty_Effect formula is [2020's qty - 2019's qty) * 2020's tariff].

The biggest issue is to build these measures since the information are in the same columns but different rows. Does anyone knows how to deal with it properly? 

[Anonymous]

Hi @Anonymous 

I think the product in the first column should be the same, not two different ones. Effects should be calculated for the same products, as far as I'm aware. One has to compare apples to apples... You deal with this as you do with everything in DAX: by using the right filters. Since you've got a Plan_Version column, you can filter by it, thus getting the values you want. Filtering can be done via slicers in the UI or inside measures. You've got a full control over this last bit.

Would you please give us more data to work with? Can be a table pasted into here or a link to a file in some shared service (Dropbox, Google Drive, OneDrive...). Also, it would be helpful if you showed us where and how you'd like to use such measures. The 'how' is quite important. You can fake something in Excel and just paste a picture of the HOW.

Thanks.

[Anonymous]

Thank for the reply!

Yes, you are right about the product column. The effects must be calculated for the same product.

I've made an excel table with more data, and how I need it to be shown in my dashboard. I need the effects for each product based on the difference between Plan a(2019) and Plan B(2020).

This table is available in the link below:

https://drive.google.com/file/d/1HgX2vB159xZa6vM9UzvWQApXbNBIEXLB/view?usp=sharing

Thanks!!