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.

amitchandak's avatar
amitchandak
Super User
5 years ago

Pikachu-Power , In this blue and yellow should both selected in slicer or none.

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

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

Hello all,

 

I have a sample pbix file in onedrive. But how to load it here? 🤔

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

    hi all,

     

    i couldnt load the pbix file without publihing private data so i will load the images:

     

     

    you also have to create a calender table and connect with the table. For table i created following two measures:

     

    Clients_Blue = CALCULATE(SUM(Tabelle[Value]),
    Tabelle[Layer] = "Blue",
    FILTER(Calender, Calender[Month] = MAX(Calender[Month])))
     
    Clients_Yellow = CALCULATE(SUM(Tabelle[Value]),
    Tabelle[Layer] = "Yellow",
    FILTER(Calender, Calender[Month] = MAX(Calender[Month])))
     
    What I want is to divide the 1800 through 4100. In best case in the KPI and without doing any transformation with the dataset.