Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
DamianDavies
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

 

DamianDavies_1-1695338478358.png

 

 

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

 

 

 

1 ACCEPTED SOLUTION
DamianDavies
Frequent Visitor

I redeveloped the requirements using PowerQuery rather than measures.

 

d;)

 

 

View solution in original post

4 REPLIES 4
DamianDavies
Frequent Visitor

I redeveloped the requirements using PowerQuery rather than measures.

 

d;)

 

 

_elbpower
Resolver III
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])
    )
)



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

 

DamianDavies_0-1695809598471.png

 

Did I do it correctly?

 

The second measure is another attempt to get a total.

 

 

lbendlin
Super User
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.

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.