Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Percent between 2 cards

Hello Power Bi experts,   I have currently trouble to get the % difference between 2 cards of data. Each card number comes from an AVERAGEX calculation : AverageCounterMeasure = VAR __Table = S...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Found the solution ! 🙂 

    Test =
    VAR Filtreafter =
    SUMMARIZE(
    FILTER(ALL(AsnieresMobility);(AsnieresMobility[AlertName]="CountCars1" || AsnieresMobility[AlertName]="CountCars2") && (AsnieresMobility[Zone]=4 || AsnieresMobility[Zone]=5) && AsnieresMobility[Day]>Date(2020;03;17));
    AsnieresMobility[Day];
    "Count";COUNTROWS(AsnieresMobility)
    )
    VAR Filtrebefore =
    SUMMARIZE(
    FILTER(ALL(AsnieresMobility);(AsnieresMobility[AlertName]="CountCars1" || AsnieresMobility[AlertName]="CountCars2") && (AsnieresMobility[Zone]=4 || AsnieresMobility[Zone]=5) && AsnieresMobility[Day]<=Date(2020;03;17) && AsnieresMobility[Day]>Date(2020;03;14));
    AsnieresMobility[Day];
    "Count";COUNTROWS(AsnieresMobility)
    )
    RETURN
    (AVERAGEX(Filtrebefore;[Count])-AVERAGEX(Filtreafter;[Count]))/AVERAGEX(Filtrebefore;[Count])
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hello RobbeVL ,

 

Both cards have the same calculation based on a table : https://community.powerbi.com/t5/Desktop/Group-by-date-amp-filter/m-p/1017995#M481119 

Here is the calculation used : 

Average Measure = 
  VAR __Table = 
    SUMMARIZE(
      'Table',
      [Date no Timestamp],
      "Count",COUNTROWS('Table')
    )
RETURN
  AVERAGEX(__Table,[Count])

 

The only difference between both cards are the filters applied. 

On the first one :

Filter1Filter2Filter3

 

 

 

 

 

 

 

 

AlterName =CountCars1 & CountCars2

Zone = 4 & 5

Day = after 14/03 && before 17/03

 

On the second one, same filters exept that the Day = after 17/03.

Thanks for your help,

 

Antoine

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I tried this, which seems to be a good way to get one of the numbers:

Test =
VAR __Table =
SUMMARIZE(
AsnieresMobility;
AsnieresMobility[Day];
"Count";COUNTROWS(AsnieresMobility)
)
RETURN
AVERAGEX(FILTER(__Table,_Table[Day]>17/03 && _Table[Zone]=3 && Table[Zone]=4 && _Table[AltertName]=CountCars);[Count])

But impossible to filter a variable... 

Any idea how I can get it ?

 

Antoine

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Found the solution ! 🙂 

    Test =
    VAR Filtreafter =
    SUMMARIZE(
    FILTER(ALL(AsnieresMobility);(AsnieresMobility[AlertName]="CountCars1" || AsnieresMobility[AlertName]="CountCars2") && (AsnieresMobility[Zone]=4 || AsnieresMobility[Zone]=5) && AsnieresMobility[Day]>Date(2020;03;17));
    AsnieresMobility[Day];
    "Count";COUNTROWS(AsnieresMobility)
    )
    VAR Filtrebefore =
    SUMMARIZE(
    FILTER(ALL(AsnieresMobility);(AsnieresMobility[AlertName]="CountCars1" || AsnieresMobility[AlertName]="CountCars2") && (AsnieresMobility[Zone]=4 || AsnieresMobility[Zone]=5) && AsnieresMobility[Day]<=Date(2020;03;17) && AsnieresMobility[Day]>Date(2020;03;14));
    AsnieresMobility[Day];
    "Count";COUNTROWS(AsnieresMobility)
    )
    RETURN
    (AVERAGEX(Filtrebefore;[Count])-AVERAGEX(Filtreafter;[Count]))/AVERAGEX(Filtrebefore;[Count])