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 =
SUMMARIZE(
AsnieresMobility;
AsnieresMobility[Day];
"Count";COUNTROWS(AsnieresMobility)
)
RETURN
AVERAGEX(__Table;[Count])
But on each card, I apply different filters.
Is there a way to calculate between 2 cards values ?
Do I need to include the filters on my first AverageX calculation and how can I do it ?
 
I hope someone helps me find the solution, I can't get it done for now...
Kind regards,
 
Antoine
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])

4 Replies

  • RobbeVL's avatar
    RobbeVL
    Impactful Individual
    6 years ago

    Hi There,

    This is ofcourse possible.
    To do that you'll need to define your filters in your measure. 

     

    Could you show the calculation of the both cards? 

    Robbe

    • 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