Forum Discussion

lucas105's avatar
lucas105
Advocate II
6 years ago
Solved

Workaround around default M2M calculation

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: 

 

ProductIDStoreIDArea
1110
21

20

2220
3315
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 

Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

1 Reply

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    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.