Check Pricelist to another pricelist on item

[SvenvandenHil]

I have a table with itemnumbers and there prices. Only there are multiple pricelists. In below PowerBI file there is an example for 6 of those pricelists on itemnumber level. I want to check 1 pricelist to another pricelist and give a percentage difference.

TestPrices 

I made 2 distinct tables to use in a filter visual. And made 2 DAX formulas to create a price for both filter tables. But the DAX formulas now also filter on both filter visuals. But what I want to achieve is that if I use filter 1, the column PriceDim1 changes and PriceDim2 will be zero. And if I use filter 2, PriceDim2 changes and PriceDim1 is zero. So that I can see the differences between 2 pricelists. And the next thing would be nice is to calculate this difference in absolute and percentages amounts, in an extra column.

[AlanFredes · Accepted solution]

Hi @SvenvandenHil ,

Given how filtering context works in Power and the current relationship setup you have in the model it isn't possible to build what you are looking for.

But Power BI has a DAX function that allows you to filter (or relate) a table without using relationships through the TREATAS function.

You would need to break the relationships you built and add in TRETAS to the DAX measures you created. There will also be a restriction in the PriceCustomerGroups you filter forcing you to always have one selected.

I made the changes in your file and you can find the updated version in the bellow link.

20240729 TestPrices Alan Fredes 

Let me know if you have any questions.

Regards,
Alan Fredes

Did I answer your question? Mark my post as a solution!

[SvenvandenHil]

Hi Alan,

Thank you, worked like a charm.

Kind regards,

Sven