Forum Discussion

jpbi23's avatar
jpbi23
Helper I
2 years ago
Solved

Power bi Sub Total wrong

here is my current table, I have one opportunity. The split % for the opportunity is getting divided per team member accordingly. Split NARR calculates the amount based on the split %. All is correct...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi  jpbi23 ,

     

    Measure follows the context of the "Total" row and is calculated in that context. Therefore, using a measure in a column of a table visualization may have unexpected values in the "Total" column.

    You can use the following measure, using the IF()+HASONEVALUE() function, to determine if it is Total, and if it is, you can calculate it according to your own needs, such as Average, Sum, etc.

     

    You can try using the following measure.

    Measure =
var _table=SUMMARIZE('Table','Table'[Opportunity ID],"Value",[Split NARR])
return
IF(
    HASONEVALUE('Table'[Opportunity ID]),[Split NARR],SUMX(_table,[Value]))

     

    Best Regards,

    Liu Yang

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

PijushRoy's avatar
PijushRoy
Community Champion
2 years ago

Hi jpbi23 

Please try below code

 

=if(
Hasonevalue('Table'[Oppourtunity ID]),
SUM('MASTER_PRODUCT'[NEW_PRODUCT_ARR_FX_ADJUSTED]) *
                       AVERAGE(MASTER_OPPORTUNITY_SPLIT[SPLIT_PERCENT])/100),
SUM('MASTER_PRODUCT'[NEW_PRODUCT_ARR_FX_ADJUSTED]))

 

 

jpbi23's avatar
jpbi23
Helper I
2 years ago

still doing the same thing, 

 

It's taking the sum of NEW_PRODUCT_ARR_FX_ADJUSTED and it's dividing by the Average of SPLIT_PERCENT. So 2,057,0821.49 / 50 = 1,028,910.75