Hello peoples!
I want to calculate the percentage of X within a category and within a date, excluding 'total'-rows.
The measure works:
Measure = DIVIDE(SUM(FactsTable[Value]),CALCULATE(SUM(FactsTable[Value]),ALLEXCEPT(FactsTable,FactsTable[Category1]),ALLEXCEPT(FactsTable,FactsTable[Date]),FactsTable[Category2]<>"TOTAL"))
But this measure does not work:
Measure = DIVIDE(SUM(FactsTable[Value]),CALCULATE(SUM(FactsTable[Value]),ALLEXCEPT(FactsTable,FactsTable[Category1]),ALLEXCEPT(DateTable,DateTable[Date]),FactsTable[Category2]<>"TOTAL"))
The only difference is, that in the one that does not work, I am using the DateTable. I use the DateTable in many calculations and visualisations, and it works perfectly. Except for here, for some reason?
Why wouldn't the second measure work, and how could I fix that?
Thank you in advance!
EDIT: Okay, the opposite solution (indicate all columns you do want to filter on, instead of the columns you do not want to filter on) seems to work:
Measure = DIVIDE(SUM(FactsTable[Value]),CALCULATE(SUM(FactsTable[Value]),ALLSELECTED(FactsTable[CategoryA],FactsTable[CategoryB],FactsTable[CategoryC],FactsTable[CategoryC],FactsTable[CategoryD],FactsTable[CategoryE]),FactsTable[Category2]<>"TOTAL"))
Although, this doesn't work entirely correctly; I think I am getting the correct values in a matrix and in a scatter chart, but not in a scorecard (used as tooltip). How could I fix that?
And why did the first measure not work?
EDIT2: And, as a side question; is it possible to add a diagonal line to a scatter chart?
I want to see which points are above or below the diagional, so a trend line is useless.
Inserting a line and rotating it by 135 degrees is also useless, as the line is not drawn from corner to corner, so it is impossible to align this properly with the scatter chart.
