topic Re: Measure totalling issue in DAX Commands and Tips

Measure totalling issue

DamianDavies — Fri, 22 Sep 2023 00:07:59 GMT

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

)

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;)

Re: Measure totalling issue

lbendlin — Tue, 26 Sep 2023 12:47:42 GMT

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

Re: Measure totalling issue

_elbpower — Tue, 26 Sep 2023 13:01:56 GMT

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

Re: Measure totalling issue

DamianDavies — Wed, 27 Sep 2023 10:16:03 GMT

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.

Re: Measure totalling issue

DamianDavies — Wed, 04 Oct 2023 01:26:24 GMT

I redeveloped the requirements using PowerQuery rather than measures.

d;)