Help with Market Share calculation in DAX

[javif_84]

Hi guys,

I am getting mad with this one... Hope you can help. Basically I need a table showing 

Years,MS%, %GR in a same table so I create them as Values and add them in the matrix. I get something like that, it is a big table but my bosses want it like that and I am a good soldier 😞

OK so I created a MAT both LY  and LAST MAT.  See my Dax that get me the correct result 

OK so far so good it gets me the correct result... 

The problem gets when I am calculating the % MS over this. With the Years my formula works fine 

But then with the MS of the MAT it gets a wrong result as 150M/658M should be 22,8% and not 21,56% I get... not sure what is the issue

This is my DAX formula with MS that does not work with MAT

Please help

Best

Javier

[Ritaf1983]

Hi @javif_84 

Please provide a workable sample data and your expected result from that. It is hard to figure out what you want to achieve from the description alone.  

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[javif_84]

See a sample PBIX from the data with all my formulas etc https://we.tl/t-XOy9qreMEU

As you may see now with this limited set of data my formula for MS for MAT LAST "MAT %MS" works (it always happens) but for my dataset it does not work out. Is there another way to calculate MS?

I cannot send you my real dataset as it is sensitive information.

Not sure what can be happening

[javif_84]

Hi Rita

See a PBIx sample with Company A/B, dummy data

As it always happen now the formula works but in my sample the LAST MAT MS% does not work, not sure why... 

See the link in WeTransfer https://we.tl/t-ktB8dxPav8

Thanks 

Best

Javi

[javif_84]

OK sorry even in this dataset the formula does not work which is great 🙂

Absolute figures and MS

This is what I get in Excel

[v-linhuizh-msft · Accepted solution]

Thanks for the reply from Ritaf1983.

Hi @javif_84 ,

Please try the following DAX formula:

LAST MAT MS%1 = DIVIDE([LAST MAT],CALCULATE([LAST MAT],ALLSELECTED('Output')))

Result:

Best Regards,
Zhu

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

[javif_84]

Thanks so much Zhu! One question, what was the issue? Why a sumx wasn t giving me the proper result?

[v-linhuizh-msft]

Hi @javif_84 ,

In DAX, measure is computed based on context, which means that they determine their values based on the current filter conditions and the context. Directly referencing a measure ensures that it is calculated correctly in the current context. SUMX function is an iterative function that calculates and sums each row in a table. Using SUMX function is summing each row. But the calculation of the measure depends on the context of the entire visual rather than a single row of data.

Best Regards,
Zhu

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