Price Volume Mix Analysis on Source Level goes wrong

[marsclone]

Hi

For our company I made a price volume and mix analysis.
On the level of productgroup and productgroup / product the values and totals are displayed correctly.

However, when I also add the source, the values and totals no longer match what is correctly displayed on product group and product level.

I can't figure out how to solve this. I hope you find the solution?

Below is the link to the test file.

PVM 

[v-janeyg-msft]

Hi, @marsclone 

It’s my pleasure to answer for you.

According to your description,I think the problem doesn't seem to be caused by adding the 'source' column.

It seems that after your formula is filtered in the filterpane, there is a problem with the hierarchical structure, resulting in data errors. Can you share your calculation logic so that we can help you revise the formula.

02. Volume effect Artikel = IF(HASONEVALUE(Artikel[Artikel]),[Volume effect1],SUMX(SUMMARIZE(VALUES(Artikel[Artikel]),Artikel[Artikel],"ABCD",[Volume effect1]),[ABCD]))

03. Productmix effect Artikel = IF(HASONEVALUE(Artikel[Artikel]),[ProductMix1b],SUMX(SUMMARIZE(VALUES(Artikel[Artikel]),Artikel[Artikel],"ABCD",[Productmix1b]),[ABCD]))

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[marsclone]

Hi @v-janeyg-msft 

Thanks for looking with me!

The basic calculation for the formula "03. Product mix effect article" is:

Productmix1b = IF([Prijs LY]=0, [Tonnen CY All] * ([Prijs CY]-[Prijs LY All]) * (([Tonnen CY]/[Tonnen CY All]) - ([Tonnen LY]/[Tonnen LY All])),[Tonnen CY All] *([Prijs LY]-[Prijs LY All]) * (([Tonnen CY]/[Tonnen CY All]) - ([Tonnen LY]/[Tonnen LY All])))

However, with this formula, the final totals are not calculated correctly. I solved this by including this formula in an "IF(Hasonevalue)" formula.

When I add the product group and/or product in a matrix, the correct values are displayed. When I add the source, the correct values are not displayed.

Based on your message, I also see that when I apply a filter, the results show a wrong value, while a filter should not change an outcome?

What is wrong with the basic calculation?

[v-janeyg-msft]

Hi, @marsclone 

You have nested a lot of measures in the measure, and they are all directly represented by divide and sum, which causes the value to change at different contexts.

There seem to be dozens of measures in your tables.,and I can't tell which one is needed based on the name.

Could you please share some sample data that need to be used?So we can help you soon.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[marsclone]

Hi @v-janeyg-msft 

There are a lot of calculations in one formula yes. Maybe there is a simple way?

I tidied up the test file so that only the relevant items remained.

I hope with your help, that a stable solution will be found?

Kind regards,
Marcel

Test File 

[marsclone]

@amitchandak  @veenashenolikar 

I see that in the model the item with description source is not the right item. I have adjusted the descriptions a bit, so that the item source is actually the correct item.

I think all connections are correct?

When I look at Productgroup1, the values are calculated correctly. If I add the item Product here, the values for Product2/3/5 are calculated correctly.

However, if I then add Source and look at Product2, the results are not correct. Now I don't know what the correct values should be either, I trust the formula that has already worked correctly twice.

I have added a new test file.

Thanks for looking with me!

Testfile 

[amitchandak]

@marsclone , When I added source to the matrix, It does not change the value of the first two measure.

Can you share the wrong output and expected output in table format?

[veenashenolikar]

@marsclone 

Are the tables whose columns make the matrix all connected in the data model?. It seems the some tables in your model are disconnected.