Forum Discussion

lulcasl's avatar
lulcasl
Frequent Visitor
4 years ago
Solved

How to calculate weighted average between two measures?

My problem is pretty much what the title says.

 

I have two measures and I need the row context to keep the same, but the subtotal context to be a weighted average, I'm stuck on this for about 2 weeks now.

 

The expected result is this:

 

 

This is the result I'm getting

 

 

Can someone give me any insight on what I'm doing wrong? I can't see any way out of this atm.

 

The spreadsheet I used as example:
https://www.mediafire.com/file/mnx9ugvii065ath/Example.xlsx/file

 

The PBIX data for easier understanding of the problem:
https://www.mediafire.com/file/1e7494i840lr0j1/Example.pbix/file

 

 

Thanks in advance!

 

 

  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi lulcasl ,

     

    Please try the following formula, on my computer, it takes only about 2 seconds to calculate the result.

     

    Measure = 
IF (
    HASONEVALUE ( d_Tipos[Tipo] ),
    [Produtividade Calculada SAP],
    SUMX (
        ALLSELECTED ( d_Tipos[Tipo] ),
        [Produtividade Calculada SAP] * [Volume Realizado]
    ) / [Volume Realizado]
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    lulcasl , try a new measure like

    Divide(sumx(Table,[ProdCal]*[Volume realizado]), Sum(Table[Volume realizado]) )

    • lulcasl's avatar
      lulcasl
      Frequent Visitor
      4 years ago

      Hello amitchandak 

       

      It worked, but now the measure is pretty much unusable, it's taking around 3 - 5 minutes to load cards and matrixes with the said measure.

       

      Anything I can do to avoid this?

      • v-kkf-msft's avatar
        v-kkf-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi lulcasl ,

         

        Please try the following formula, on my computer, it takes only about 2 seconds to calculate the result.

         

        Measure = 
IF (
    HASONEVALUE ( d_Tipos[Tipo] ),
    [Produtividade Calculada SAP],
    SUMX (
        ALLSELECTED ( d_Tipos[Tipo] ),
        [Produtividade Calculada SAP] * [Volume Realizado]
    ) / [Volume Realizado]
)

         

        If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
        Best Regards,
        Winniz
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.