Summarise Function - How to apply to current filters?

[knowledgegarage]

Hi,

I have a measure as such:

Share (%) = SUMX(SUMMARIZE(Master, Master[Holdings], Master[ID], "Subtotal 2",

IFERROR(CALCULATE([Holdings Value]*[Normalised Weight])/SUM(Master[Market Cap]),0)), [Subtotal 2])

It works nicely, by calculating at each row (ID) for a given collection (Holding). However I now want to introduce another column 'Sector' I tried to amend the calculation above to:

Investor Stake (%) = SUMX(SUMMARIZE(Master, Master[Holdings], Master[Sector], Master[ID], "Subtotal 2",

IFERROR(CALCULATE([Holdings Value]*[Normalised Weight])/SUM(Master[Market Cap]),0)), [Subtotal 2])

This calculation works when I filter per sector but when I look at the overall it effectively adds up the investor stake (%) for each by sector calculation instead of computing for the whole data again.

Any ideas on what I am missing?

[knowledgegarage]

Ok. So I have investigate the issue and I think I have found what is causing the calculation to be wrong at the overall level.

I have a measure which is designed to sum the weights of the holdings:

Coverage % Total = CALCULATE([Coverage %], ALLEXCEPT(Master,Master[Holdings],Master[Attribute]))

To do this at the sector level it only works when I add in Master [Sector]

Coverage % Total = CALCULATE([Coverage %], ALLEXCEPT(Master,Master[Holdings], Master[Sector],Master[Attribute]))

This is what causes the explosion in calculation. Is it possible that changing this measure blows up the number of rows being calculated?

[tamerj1]

Hi @knowledgegarage 

You can use the first formula and still be able to filter the sector column. I'm still not able to understand the need for the new formula.