topic Re: [Sales portfolio] DAX expression to create dynamic product share based on slicer selection in DAX Commands and Tips

[Sales portfolio] DAX expression to create dynamic product share based on slicer selection

powerapprentice — Thu, 29 Feb 2024 15:14:28 GMT

Hi all,

I have a sales portfolio for which I would like to write a measure in DAX that returns the dynamic share of one or multiple products based on the selection via a slicer in list form.

The portfolio consists of products A, B, C with a turnover of 30 each. In sum, we'd have a turnover of 90 and a share of 1/3 per product. If I select product A and B now, my current setup in DAX would still return a share of 1/3 for each, A and B. However what I want it to return is a share of 1/2 per product as the combined turnover based on the selection in the slicer now is 60 instead of 90 as product C is not considered. The shares are displayed in a bar chart with each product on the x-axis and the mixes on the y-axis.

I would really appreciate if someone could help me! If required, I can also post my (false) code that currently returns the incorrect result.

Thanks a lot.

Re: [Sales portfolio] DAX expression to create dynamic product share based on slicer selection

Chakravarthy — Thu, 29 Feb 2024 17:36:18 GMT

Hi powerapprentice - Please create a meaure with the below code

Measure =

VAR SAL = CALCULATE(SUM('Table'[Sales]),ALLSELECTED('Table'[Column1]))

VAR DIV = DIVIDE(SUM('Table'[Sales]),SAL,0)

RETURN DIV

Re: [Sales portfolio] DAX expression to create dynamic product share based on slicer selection

powerapprentice — Fri, 08 Mar 2024 13:41:06 GMT

Thank you very much for your response Chakravarthy, and please excuse my delayed response. I was not able to try out your suggestion until now.

Your solution works for getting the dynamic mix, but if I want to calculate the mix deviation between the sales portfolio of the current and the previous year via the following formula

Mix deviation = (VAR SAL) * (SALES PRICE_PreviousYear) * (VAR DIV_CurrentYear - VAR DIV_PreviousYear)

which includes your solutions VAR SAL and VAR DIV, I get an error message saying that there is a circular dependency between VAR DIV_CurrentYear, Mix deviation, and VAR DIV_CurrentYear.

Do you know what could cause this error?