Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
5 years ago

WEIGHTING CALCULATION ON MEASURES

Hello community,

Let's see if I can explain myself.

I attach a filtered pbix so that you only see the information you need. I have two KPIs based on some projects that are the SAFETY INDEX and the OPS.

For example, for YTD (I have calculations for BAT and LY as well), I need a weighted calculation when more than one project is selected. This image is correct:

kikejnt_0-1623761096400.png

But if I select more than one project, I need 18.YTD Safety Index and 26.YTD OPS to be weighed as if it were a SUMPRODUCT in excel. I need a measure that does this to me:

kikejnt_3-1623762109992.png

because in my .pbix it doesn't come out the same. The difference is minimal, but the calculation is not correct. not esoty pondering.

kikejnt_4-1623762153188.png

In the end the calculation on a card for 18.YTD Safety Index should look something like this:

IF(COUNTROWS('AFO Global Report'[Project Code]=1,"what I have already calculated"," what I am lookingfor")

attached pbix.

https://1drv.ms/u/s!AggHtWEAB2tKgWYE_tTGhDd8YLm9?e=fwVEL9

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Syndicate_Admin , You have to try like

    Assume you have two measures [YTD Safety],[YTD OPS] , try measure like

     

    divide(sumx(values(Table[gt_rel_elemento]), [YTD Safety],[YTD OPS]),[YTD OPS])

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      5 years ago

      This does not work. In fact, the formula is not right. SUMX does not let you put two expressions and you are putting them: divide(sumx(values(Table[gt_rel_elemento]), [YTD Safety],[YTD OPS]),[YTD OPS])

      Can you upload the pbix with the expected result you've done?

      The interesting thing would be to include this calculation in the formula of:

      18.YTD Safety Index =

      CALCULATE(IF([17. TOTAL AVERAGE SUM PER TEMPLATE YTD]=0.0,IF([17. TOTAL AVERAGE SUM PER TEMPLATE YTD]<=110,10-[17. TOTAL AVERAGE SUM PER TEMPLATE YTD]/110*4,7-[17. TOTAL AVERAGE SUM PER TEMPLATE YTD]/393*7)))
      I don't know how to do it. The way you say it doesn't make me the right calculation.
    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      5 years ago

      I return to this thread since no one has been able to give me light.

      What I want is to make a weighting. (Safety Index * Inspections)/Total Inspections.

      According to the photo I attach. The first line should be 9.94*16/202.

      The second would be: 9.67*6/202.

      etc

      How do I do this in one measure?

      kikejnt_0-1624870022579.png