cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## 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]))RETURNIF(    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])RETURNIF(    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;)

1 ACCEPTED SOLUTION
Frequent Visitor

I redeveloped the requirements using PowerQuery rather than measures.

d;)

4 REPLIES 4
Frequent Visitor

I redeveloped the requirements using PowerQuery rather than measures.

d;)

Resolver III

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])
)
)``````

Frequent Visitor

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.

Super User

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-...
Please show the expected outcome based on the sample data you provided.