Forum Discussion

ItoDiaz's avatar
ItoDiaz
Icon for Helper I rankHelper I
2 years ago
Solved

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) )

 

 

  • Anonymous's avatar
    Anonymous
    2 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

  • Anonymous's avatar
    Anonymous
    Not 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

  • 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.