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 ,

 

Are the numbers in your post the expected results you want? If so, can you give an example of how to get this line of results in conjunction with your example data (REPLY 2)?

2021 1 Site1 % Training Done during the Quarter 16.46%

 

Best Regards,

Neeko Tang

abanar2's avatar
abanar2
Icon for Helper I rankHelper I
2 years ago
20211Site1% Training Done during the Quarter16.46%

= 

Dividend row:

YearQuarterSite_IDKPI_IDKPI_NameIs_Total_KPIKPI_GroupKPI_ValueDividend_IDDivisor_ID
20211Site1KPI1Training PlannedYes% Training Done during the Quarter79KPI2KPI1

 

Divisor row:

20211Site1KPI2Training DoneNo% Training Done during the Quarter13KPI2KPI1

 

13 / 79 = 0.164556962 

  • 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.