Sum For All in Power bi

[Anonymous]

Hi, I was having trouble with this calculation. I have tried many ways but I'm not getting the right answer. I'm confused with using measures of column too.....

such as CALCULATE( DIVIDE(SUM(CA_FACT_MGMT_SUM[NPT_HRS]),SUM(CA_FACT_MGMT_SUM[COMPLETION_HRS]) ))

SUM(CA_FACT_MGMT_SUM[NPT_HRS])/SUM(CA_FACT_MGMT_SUM[COMPLETION_HRS])

CALCULATE( SUMX( CA_FACT_MGMT_SUM, CA_FACT_MGMT_SUM[NPT_HRS]/CA_FACT_MGMT_SUM[COMPLETION_HRS]))


this is my pbix file https://1drv.ms/u/s!AoKh3nlpMpSOgmP2W6IHiupBOk3U?e=zHodcX

I actually want a straight average line based on that formula. The expected output for year 2020 is 9.7%.

[StefanoGrimaldi]

hi, I would reccomend you to post a example data input and the expect result and explain what you want the measures to do exactly, for a better response.

[Anonymous]

@StefanoGrimaldi hi. This is my pbix file. For that formula, I need a straight average line to include in this chart. For year 2020, the AVG NPT % should be 9.7%. Appreciate if you could help me

https://1drv.ms/u/s!AoKh3nlpMpSOgmP2W6IHiupBOk3U?e=zHodcX

[v-easonf-msft · Accepted solution]

Hi， @Anonymous 

Try measure as below:

NPT_%_new =
CALCULATE (
    SUM ( Sheet1[NPT_HRS] ) / SUM ( Sheet1[COMPLETION_HRS] ),
    ALLEXCEPT ( Sheet1, Sheet1[YEAR] )
)

 

Refer to sample file for more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.