Power bi Sub Total wrong

[jpbi23]

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 but the total for Split NARR. 

here is my current dax for Split NARR,

Split NARR = SUM('MASTER_PRODUCT'[NEW_PRODUCT_ARR_FX_ADJUSTED]) *

                       AVERAGE(MASTER_OPPORTUNITY_SPLIT[SPLIT_PERCENT])/100

How do I get the total for Splitt NARR correct? It should be 145,672.49

[Anonymous · Accepted solution]

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]

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]

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