cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pfroide Frequent Visitor
Frequent Visitor

Differents ways to calculte total with measure

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)

1.png

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)

2.png

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.

8 REPLIES 8
Super User II
Super User II

Re: Differents ways to calculte total with measure

@pfroide  - What are the formulas for:

[temps_heures_supp]
and
[effectif_reel]

pfroide Frequent Visitor
Frequent Visitor

Re: Differents ways to calculte total with measure

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.

Super User II
Super User II

Re: Differents ways to calculte total with measure

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.

pfroide Frequent Visitor
Frequent Visitor

Re: Differents ways to calculte total with measure

 

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 :3.png

 

When I unchek May and June :

 

4.png

 

There is definitely something wrong with them !

Super User II
Super User II

Re: Differents ways to calculte total with measure

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

pfroide Frequent Visitor
Frequent Visitor

Re: Differents ways to calculte total with measure

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

Super User II
Super User II

Re: Differents ways to calculte total with measure

@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

pfroide Frequent Visitor
Frequent Visitor

Re: Differents ways to calculte total with measure

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 !

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors