Workaround around default M2M calculation

[lucas105]

Hi all, 

I'm struggling with a bit of an issue as I'm trying to figure out a workaround to avoid default Tabular's behaviour. 

So, my scenario is somehow like this: 

ProductID	StoreID	Area
1	1	10
2	1	20
2	2	20
3	3	15
Total		45

As I have my fact table on product level and then I have to join it to product dimension from which it's being populated to stores dimension. 

So based on fact table I don't know how much area a given product occupies in a store, but since it's constant, I want to calculate total Area for calculation of share. 

The problem is that as Tabular will give me 10 for ProductID = 1 in any store it's in, my Total is not correct for my requirement. I have to calculate total based on everything, so I'd love to have 65 as my base for calculation of share, instead of 45. 

Can you help me? 

Best,

Luc 

[Icey · Accepted solution]

Hi @lucas105 ,

Try to create a Measure like so:

calculation of share 2 = SUM ( 'Table'[Area] ) / SUMX ( ALL ( 'Table' ), 'Table'[Area] )

If I understand it wrong, please let me know.

Best Regards,

Icey

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