Forum Discussion

Latermocdelo's avatar
Latermocdelo
New Member
4 years ago

How to calculate Average with Filters

Good morning,

 

I'm currently Power BI beginer and I try to learn how to use it in the good way.

 

I try to calculate average of error percentage with filters.

 

This is my data tables :

'Table test FX9600' :

 

'Table test LRU1002':

 

And my results table :

 

 

I want to calculate the average of error percentage (column:"Moyenne % erreur") with specific "Session" and "Type de lecteur".

The data is on my two other tables.

I try to solved this problem with the "SWITCH" command, but I think there is a better (faster) way to do this.

 

This is my command which works :

 

Moyenne % Erreur = SWITCH('Param Session'[Type de lecteur],
    "FX9600", SWITCH( [Session], 
                    "Session 0", CALCULATE(AVERAGE('Table test FX9600'[Erreur %]),'Table test FX9600'[Session]="Session 0"),
                    "Session 1", CALCULATE(AVERAGE('Table test FX9600'[Erreur %]),'Table test FX9600'[Session]="Session 1"), 
                    "Session 2",CALCULATE(AVERAGE('Table test FX9600'[Erreur %]),'Table test FX9600'[Session]="Session 2")
                ),
    "LRU1002",  SWITCH( [Session], 
                    "Session 0", CALCULATE(AVERAGE('Table test LRU1002'[Erreur %]),'Table test LRU1002'[Session]="Session 0"),
                    "Session 1", CALCULATE(AVERAGE('Table test LRU1002'[Erreur %]),'Table test LRU1002'[Session]="Session 1"), 
                    "Session 2",CALCULATE(AVERAGE('Table test LRU1002'[Erreur %]),'Table test LRU1002'[Session]="Session 2")
                ),
..........
)

 

 

Someone has any idea to simplify (and automate) this ?

Thanks in advance

Quentin

1 Reply

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support

    Hi, Latermocdelo 

    For the nesting of 'switch' in this measure, I'm afraid it can't be simplified further in DAX.

    Best Regards,
    Community Support Team _ Eason