Differents ways to calculte total with measure

[pfroide]

Hi,

I'm new in PowerBI so maybe my question is not very clear. If so, please tell me and I'll try to be more explicit.

I've created a Measure and its behavoir seems different in two tables. It is called "Moyennes heure supplémentaires". In this first picture, you can see that the total is the mean of all valeus, that's correct and that's the behavior I want.

Edit : I juste saw it's not the mean, it's something else but I don't know what (mean(14,93 + 9,5 + 13,06 + 9,81 + 8,13 + 5,31 + 6,92 + 8,63 + 9,08) = 9,45 <> 9,61)

In this second, the behavior is different, and the total is a sum of the values.

Edit : I juste saw it's not the sum, it's something else but I don't know what (18,43 + 13,27 + 12,31 + 13,22 = 57,23 <> 54)

Why is this happening ? And mot importanty, how can I tell the behavior I want for a measure ?

The measure is composed by other mesure, as you can see :

moyenne_heures_supp = 
IFERROR ( CALCULATE ( [temps_heures_supp] / [effectif_reel] ); BLANK () )

Thanks in advance for your help.

[pfroide]

Hey,

I succeded to do what I wanted by using the formula HASONEFILTER.

WIth this function I can have two different formulas : one for the rows with filter, and another one for the total row.

Thanks for your help !

[Anonymous]

@pfroide  - What are the formulas for:

[temps_heures_supp]
and
[effectif_reel]

[pfroide]

Hi,

There are the two formulas, based on measures too :

temps_heures_supp = CALCULATE([total_temps_travaille_1]-[temps_standard_travaille])

effectif_reel = CALCULATE([effectif_theorique] - [effectif_maternite] - [effectif_maladie])

And maybe you will need these too :

total_temps_travaille_1 = CALCULATE([temps_chargeable]+[temps_nonchargeable])

temps_chargeable = 
VAR calcul =
    CALCULATE (
        SUM ( lignes_temps_services[nb_heures] );
         OR (
            lignes_temps_services[id_projet] < 1107;
            lignes_temps_services[id_projet] > 1116
        )
    )
RETURN
    IF ( calcul > 0; calcul; 0 )

temps_nonchargeable = 
CALCULATE (
    [tps_projet] + [tps_administration] + [tps_disponibilite] + [tps_maladie] + [tps_cp_exc] + [tps_marketing] + [tps_formations]
)

tps_administration = 
VAR calcul =
    CALCULATE (
        SUM ( lignes_temps_services[nb_heures] );
        lignes_temps_services[id_projet] = 1109
    )
RETURN
    IF ( calcul > 0; calcul; 0 )

This last one is very similar to all the ones that allow me to calculte temps_nonchargeable, only the id changes

Maybe you need all of them ?

Thanks.

[pfroide]

I saw this strange behavior too.

The total for 'effectif_reel' and 'moyenne_heures_supp" don't change as I except them to do when a filter with the month is checked.

With all months on :

When I unchek May and June :

There is definitely something wrong with them !

[Anonymous]

@pfroide  - I think you should troubleshoot all of the measures that these measures depend on, and see where it goes wrong.

[pfroide]

I think my measures are good because all values are correct. Only the "total" are not.

[Anonymous]

@pfroide - The "Total" behavior depends on the expressions - so if the Total is wrong, the measure is wrong. What you need to figure out is when it goes wrong. 

DAX works by applying the relevant filters to the calculation. One potential issue with your measures is the logic that sets it to zero if it's not greater than zero. This could eliminate negative values for particular data points, but the negative values would actually be incorporated into the total.

Hope this helps,

Nathan

[Anonymous]

It would also be helpful to see the various measures in the visualization - the measure is question is a straight division (it would be preferable to use the DIVIDE function, but it should work the way it is). So check the values of the numerator and denominator for each row - I think that the math should work.