Reply
javif_84
Helper I
Helper I

Help with Market Share calculation in DAX

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 😞

 

javif_84_3-1730398655740.png

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

 

javif_84_0-1730397833920.png

javif_84_1-1730397864276.png

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 

 

javif_84_2-1730398032472.png

 

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

 

javif_84_4-1730399143229.png

 

Please help

 

Best

Javier

 

1 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:

vlinhuizhmsft_1-1730697129053.png

 

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. 

View solution in original post

7 REPLIES 7
Ritaf1983
Super User
Super User

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-...

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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

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

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

 

Absolute figures and MS

 

javif_84_1-1730460982344.png

This is what I get in Excel

 

javif_84_2-1730461170643.png

 

 

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:

vlinhuizhmsft_1-1730697129053.png

 

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. 

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

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.

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)