Forum Discussion

abanar2's avatar
abanar2
Icon for Helper I rankHelper I
2 years ago

Need help with my measure

Hello Community,    I’m currently working on a project where I need to calculate Key Performance Indicators (KPIs) using a DAX measure, but I’m having some difficulties and could use your help. I ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi abanar2 ,

 

We can create two tables.

Table 2 = FILTER(SELECTCOLUMNS('Table',[Year],[Quarter],[Site_ID],[KPI_Name],[KPI_Value]),[KPI_Name] IN {"Training Planned","Number of Focus Rooms in the Office"})
Table 3 = 
var _a=DISTINCT(SELECTCOLUMNS('Table',[Year],[Quarter],[Site_ID]))
var _b={"% Training Done during the Quarter"}
RETURN CROSSJOIN(_a,_b)

We create a calculated column on Table 3.

Table_KPI_Value = 
var _a=CALCULATE(SUM('Table'[KPI_Value]),FILTER('Table',[Year]=EARLIER('Table 3'[Table_Year]) && [Quarter]=EARLIER('Table 3'[Table_Quarter]) && [Site_ID]=EARLIER('Table 3'[Table_Site_ID]) && [KPI_ID]="KPI1"))
var _b=CALCULATE(SUM('Table'[KPI_Value]),FILTER('Table',[Year]=EARLIER('Table 3'[Table_Year]) && [Quarter]=EARLIER('Table 3'[Table_Quarter]) && [Site_ID]=EARLIER('Table 3'[Table_Site_ID]) && [KPI_ID]="KPI2"))
RETURN DIVIDE(_b,_a)

Then we can create a table.

Table 4 = 
var _a=SELECTCOLUMNS('Table 2',[Table_Year],[Table_Quarter],[Table_Site_ID],[Table_KPI_Name],[Table_KPI_Value])
var _b=SELECTCOLUMNS('Table 3',[Table_Year],[Table_Quarter],[Table_Site_ID],"Table_KPI_Name",[Value],[Table_KPI_Value])
RETURN UNION(_a,_b)

Best Regards,

Neeko Tang

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

abanar2's avatar
abanar2
Icon for Helper I rankHelper I
2 years ago

Hi Anonymous,

 

Thank you for your ideas.


In my real case I have about 50 KPI Groups and this number can change, so maybe in the future I will have less or more. I can`t hardcode anyting into my script because then it would be very painful to maintane. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi abanar2 ,

     

    If you want to avoid hard-coding, you can modify the DAX expression like this:

    Table 2 = FILTER(SELECTCOLUMNS('Table',[Year],[Quarter],[Site_ID],[KPI_Name],[KPI_Value]),[KPI_Name] IN SELECTCOLUMNS(FILTER('Table',[Is_Total_KPI]="Yes"),[KPI_Name]))

     

    Best Regards,

    Neeko Tang

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