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 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
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.

3 Replies

  • 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

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.