Forum Discussion

DamianDavies's avatar
DamianDavies
Frequent Visitor
2 years ago
Solved

Measure totalling issue

Hello Community 🙂

 

I am building a table to calculate the sales bonus earned by salesperson and quarter and having trouble with totalling a measure.  

 

The possible sales bonus value is based on target bands for their annual sales target and being awarded the bonus is based on hitting both sales and profit targets for the quarter.

 

You can see from the screenshot that I have had similar issues bdmActualSalesQtr and bdmActualGpQtr measures

 

 

 

I was able to solve them thanks to Greg_Deckler Measure Totals, The Final Word - Microsoft Fabric Community and others like How to Make Measures Total Correctly in Power BI Tables - ArcherPoint

bdmActualSalesQtr =
CALCULATE(
    SUM(Sales[Total Sales]),
    Sales[User Code]=SELECTEDVALUE('Incentives BDM Quarterly'[User Code]),
    'Date'[Fiscal Quarter Year]=SELECTEDVALUE('Incentives BDM Quarterly'[Fiscal Quarter Year])
    )
 
BDM Actual Sales Qtr =
VAR __table = SUMMARIZE(Sales, 'Date'[Fiscal Quarter Year], "__value", SUM(Sales[Total Sales]))
RETURN
IF(
    COUNTROWS('Incentives BDM Quarterly') = BLANK(), BLANK(),
    IF(
    COUNTROWS('Incentives BDM Quarterly') = 1,
    'Incentives BDM Quarterly'[bdmActualSalesQtr],
    SUMX(__table, [__value])
    )
)
 
But I can't get the actual bonus earned to work
 
bdmActualBonusQtr =
IF(
    COUNTROWS('Incentives BDM Quarterly') = BLANK(), BLANK(),
    IF(
    'Incentives BDM Quarterly'[BDM % GP Target] >=1 && 'Incentives BDM Quarterly'[BDM % Sales Target] >=1,
    SUM('Incentives BDM Quarterly'[BDM Sales Band Bonus Qtr]),
    0
    )
)
 
BDM Actual Bonus Qtr =
VAR __table = SUMMARIZE('Incentives BDM Quarterly', [Fiscal Quarter Year], "__value", [bdmActualBonusQtr])
RETURN
IF(
    COUNTROWS('Incentives BDM Quarterly') = BLANK(), BLANK(),
    IF(
    COUNTROWS('Incentives BDM Quarterly') = 1,
    'Incentives BDM Quarterly'[bdmActualBonusQtr],
    SUMX(__table, [__value])
    )
)
 
I really can't afford to lose any more hair over this!!  Thank you for your consideration.
 
d;)
 

 

 

 

Measure Total with VAR and IF statements
Measure Totals
  • DamianDavies's avatar
    DamianDavies
    2 years ago

    I redeveloped the requirements using PowerQuery rather than measures.

     

    d;)

     

     

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
    If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    Please show the expected outcome based on the sample data you provided.

    If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • _elbpower's avatar
    _elbpower
    Resolver III
    2 years ago

    May be replace 

     

    IF(
    'Incentives BDM Quarterly'[BDM % GP Target] >=1 && 'Incentives BDM Quarterly'[BDM % Sales Target] >=1,
    SUM('Incentives BDM Quarterly'[BDM Sales Band Bonus Qtr]),
    0
    )

     


    With

     

    SUMX(FILTER('Incentives BDM Quarterly',[BDM % GP Target] >=1 && [BDM % Sales Target] >=1),'Incentives BDM Quarterly'[BDM Sales Band Bonus Qtr])

     



    What are you trying to achieve in the second measure and can you tell which one is table column, and which one is measure.

    BDM Actual Bonus Qtr =
VAR __table = SUMMARIZE('Incentives BDM Quarterly', [Fiscal Quarter Year], "__value", [bdmActualBonusQtr])
RETURN
IF(
    COUNTROWS('Incentives BDM Quarterly') = BLANK(), BLANK(),
    IF(
    COUNTROWS('Incentives BDM Quarterly') = 1,
    'Incentives BDM Quarterly'[bdmActualBonusQtr],
    SUMX(__table, [__value])
    )
)



    • DamianDavies's avatar
      DamianDavies
      Frequent Visitor
      2 years ago

      Thank you for your reply, unfortunately, that didn't work.  It returns a blank column

       

       

      Did I do it correctly?

       

      The second measure is another attempt to get a total.

       

       

  • DamianDavies's avatar
    DamianDavies
    Frequent Visitor
    2 years ago

    I redeveloped the requirements using PowerQuery rather than measures.

     

    d;)