Forum Discussion

Pikachu-Power's avatar
Pikachu-Power
Impactful Individual
5 years ago
Solved

KPI question

Hello all,   I have following situation:     Indicator for KPI is:   KPI_Indicator = CALCULATE(SUM(Table[Value]), Table[Layer] = "Blue", FILTER(Calender, Calender[Month] = MAX(Calender...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi  Pikachu-Power  ,

    According to your description, I create this data：

    Here are the steps you can follow：

    1. Create measure.

    When the color is blue, the category is 1 and the maximum date:

    Blue_1 =
var _1=MAXX(FILTER(ALL('Table'),'Table'[Categorie1]=1&&'Table'[Layer]="Blue"),'Table'[Date])
return
CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Layer]="Blue"&&'Table'[Date]=_1))

    When the color is yellow, category is 1 and the maximum date:

    Yellow_1 =
var _1=MAXX(FILTER(ALL('Table'),'Table'[Categorie1]=1&&'Table'[Layer]="Yellow"),'Table'[Date])
return
CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Layer]="Yellow"&&'Table'[Date]=_1))

    2. Result.

     

    You can downloaded PBIX file from here.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Pikachu-Power's avatar
Pikachu-Power
Impactful Individual
5 years ago

But the SUM is missing:

 

Clients_Blue = CALCULATE(SUM(Tabelle[Value]),
Tabelle[Layer] = "Blue",
FILTER(Calender, Calender[Month] = MAX(Calender[Month])))
 
Sorry my table wasnt detailed enough. I have two additional columns Categorie1 and Categorie2 that splits the blue part in 3 tables. So I need the SUM of the MAX.
 
Another point:
I need more Filter like Table[Layer]="Blue" and Table[Layer2]="Blue2" and Table[Layer3]="Blue3". But FILTER only accept one argument. Is it possible to complete this?
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi  Pikachu-Power ,

Not very clear about your description... If you want more filters like table [layer] = "blue", you can use the in function


Step description:

1. First use the summrize function to find a virtual table, which is grouped by Layer and some of the largest values of the date:

2. Use the IN function to select the desired Layer

 

Here are the steps you can follow：

1. Create measure.

Blue_Yellow =
var _summrize=
SUMMARIZE('Table','Table'[Layer],
"value",MAX('Table'[Value]),
"date",MAX('Table'[Date])
)
return
CALCULATE(SUM('Table 2'[Value]),FILTER('Table 2','Table 2'[Layer] in {"Blue","Yellow"}))

2. Result.

 

You can downloaded PBIX file from here.

 

If my answer is not what you need, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Pikachu-Power's avatar
    Pikachu-Power
    Impactful Individual
    5 years ago

    Hello Liu,

     

    Thanks for your reply. What i am looking for is somethink like:

     

    Tab_Blue:

    SUMMARIZE('Table','Table'[Categorie1],'Table'[Layer],
    "value",MAX('Table'[Value]),
    "date",MAX('Table'[Date])
    )

     

    with 'Table'[Categorie1] = 1  and 'Table'[Layer] = Blue (maybe it is possible to restrict the summarize table?) and

     

    Tab_Yellow:

    SUMMARIZE('Table','Table'[Categorie1],'Table'[Layer],
    "value",MAX('Table'[Value]),
    "date",MAX('Table'[Date])
    )

     

    with 'Table'[Categorie1] = 1  and 'Table'[Layer] = Yellow and final use these results in a KPI. Devide Tab_Blue / Tab_Yellow should be possible.

    • Pikachu-Power's avatar
      Pikachu-Power
      Impactful Individual
      5 years ago

      PS: The SUMMARIZE dont work. If you change for example 1600 with 2600 in Table1 for Blue 01.07.2020 then Table2 takes 2600 instead of 1800. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi  Pikachu-Power  ,

      According to your description, I create this data：

      Here are the steps you can follow：

      1. Create measure.

      When the color is blue, the category is 1 and the maximum date:

      Blue_1 =
var _1=MAXX(FILTER(ALL('Table'),'Table'[Categorie1]=1&&'Table'[Layer]="Blue"),'Table'[Date])
return
CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Layer]="Blue"&&'Table'[Date]=_1))

      When the color is yellow, category is 1 and the maximum date:

      Yellow_1 =
var _1=MAXX(FILTER(ALL('Table'),'Table'[Categorie1]=1&&'Table'[Layer]="Yellow"),'Table'[Date])
return
CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Layer]="Yellow"&&'Table'[Date]=_1))

      2. Result.

       

      You can downloaded PBIX file from here.

       

      Best Regards,

      Liu Yang

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.