Forum Discussion
Bad calculated ratio at lower level matrix
Hello all,
I have created this mesure and i'm triying to use it in a matrix, ratio by etablissement and total ratio are right. My problem is that the ratio by department is not calculated in the rigth way.
In my Matrix "etablissement" is the first level and "departement" my second level.
See in the picture what I get (left) by département and what I should obtain (right).
And here my measure, what should I do to correct this?
My two table have a relationship many to many
Thanks in advance
Prorata etablissement et departement =
VAR Etablissement = SELECTEDVALUE('Plan'[ETABLISSEMENT CONSO])
VAR Departement = SELECTEDVALUE('Plan'[DEPARTEMENT])
VAR CurrentMonth = "202405" -- Remplacez cette valeur par le mois courant si nécessaire
VAR Numerator = CALCULATE( SUM('Plan '[Nb Heures Catalogue]), ALLSELECTED(FTEHC[AnnesMois]) )
VAR DenominatorPerEtablissement = CALCULATE( DISTINCTCOUNT(FTEHC[ID]), FILTER('FTEHC', FTEHC[AnnesMois] = CurrentMonth && 'FTEHC'[Position lieu_2] = Etablissement) )
VAR DenominatorPerDepartement = CALCULATE( DISTINCTCOUNT(FTEHC[ID]), FILTER('FTEHC', FTEHC[AnnesMois] = CurrentMonth && 'FTEHC'[Departement OM] = Departement) )
VAR DenominatorTotal = CALCULATE( DISTINCTCOUNT(FTEHC[ID]), FILTER('FTEHC', FTEHC[AnnesMois] = CurrentMonth) )
RETURN
SWITCH(
TRUE(),
ISINSCOPE('Plan'[ETABLISSEMENT CONSO]), DIVIDE(Numerator, DenominatorPerEtablissement),
ISINSCOPE('Plan'[DEPARTEMENT]), DIVIDE(Numerator, DenominatorPerDepartement), DIVIDE(Numerator, DenominatorTotal) )
- Anonymous2 years ago
HI ItoDiaz,
It seems like a common issue when the measure expression calculate on the different hierarchy levels.
Have you try to create a variable with summarize function to manually restore the current hierarchy aggregations levels at first? Then you can use iterator function to apply and summary these records.
For detail informations, you can check the greg's blog about measure total level calculation research.
Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
2 Replies
- AnonymousNot applicable
HI ItoDiaz,
It seems like a common issue when the measure expression calculate on the different hierarchy levels.
Have you try to create a variable with summarize function to manually restore the current hierarchy aggregations levels at first? Then you can use iterator function to apply and summary these records.
For detail informations, you can check the greg's blog about measure total level calculation research.
Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
- ItoDiaz
Helper I
Hello,
Anonymous , thank you for your answer.
I have read Greg's post and I have tried with Sumx, Summarize and Hasonevalue, nevertheless I didn't find the good solution.