cancel
Showing results for
Did you mean:
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)

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

8 REPLIES 8
Super User II

## Re: Differents ways to calculte total with measure

@pfroide  - What are the formulas for:

[temps_heures_supp]
and
[effectif_reel]

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

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

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 :

When I unchek May and June :

There is definitely something wrong with them !

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.

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

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

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.

Announcements

#### 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!

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?

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

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