Forum Discussion

PhilippeMuniesa's avatar
PhilippeMuniesa
Resolver I
3 years ago
Solved

Calculate filter, with format or value

Hello,

In a powerPivot table. "CompletContratrem"


I have a "Brut" column, decimal number format.
And a measure "fx_Brut":=Sum([Brut])

and another "Année" column, Text format

 

example

 

nomBrutAnnée 
Titi1125,452020 
Toto3456,872021 
Tata3456,212020 
tete1765,122021 

 

I would like the "Brut"  total if "Année" = 2020,

I tried several solutions, but it stuck

I have already calculated a measure to get 2020 (the smallest year in the Year column)

 

Regul_N_1:=calculate(value(max(CompletContratrem[Année]))-1)

i get good 2020

But,

even though I converted the "Année" column to integer, the metric "fx_Brut_N_1"

 

fx_Brut_N_1:=calculate([fx_Brut];filter(CompletContratrem;CompletContratrem[Année] =[Regul_N_1]))

, returns me (empty)

 

 

If I leave the Year column as text, and I adapt the measure by converting Année with Value

 

fx_Brut_N_1:=calculate([fx_Brut];filter(CompletContratrem;Value(CompletContratrem[Année] )=[Regul_N_1]))

, I also have a value return (empty)

 

On the other hand, if I modify the measure and set a hard comparison criterion ex: 2020, it works.

 

fx_Brut_N_1:=calculate([fx_Brut];filter(CompletContratrem;Value(CompletContratrem[Année] )=2020))

, returns the correct value

but,

 

fx_Brut_N_1:=calculate([fx_Brut];filter(CompletContratrem;Value(CompletContratrem[Année] )=value([Regul_N_1])))
Always returns (empty)

 

I don't understand, I need explanations and help.

Thank for your help

 

Philippe

 

2 Replies