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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
heathernicole
Continued Contributor
Continued Contributor

Odd totaling issue in Power BI Report in Bar Charts and Grid Visuals??

I have a chart that calculates bonus points for accounts that reach certain thresholds for each sales rep. 

2000 points for accounts over 10,000

4000 points for accounts over 25,000

6000 points for accounts over 50,000

 

Here's an image of the graph:

 

BonusPointsWithoutLogo.JPG 

The graph shows bonus points for almost every sales rep - when in reality only two reps have earned bonus points - grid image:

 

 

 BonusPointsSpreadsheet.JPG

 

I have tried to tackle this in a previous post: 

http://community.powerbi.com/t5/Desktop/Sum-multiple-columns-from-multiple-tables-How-to/td-p/28045/...

 

I still have a totaling issue in the reports despite trying multiple solutions. 

 

The issue is it totals columns that shouldn't be totaled which is how sales reps that don't have any bonus points are showing as they do in the chart at the top. 

 

I have to get this report right so that's why I'm taking another stab at it - to see if there's a new way to look at this and handle the odd totaling. 🙂 

 

Here's the script for the bonus points:

 

 

IF('SALES DETAILS'[New Customer Points Per Dollar] >= 10000 && 'SALES DETAILS'[New Customer Points Per Dollar] <= 24999,
2000,
IF('SALES DETAILS'[New Customer Points Per Dollar] >= 25000 && 'SALES DETAILS'[New Customer Points Per Dollar] <= 49999,
6000, IF('SALES DETAILS'[New Customer Points Per Dollar] >= 50000,
12000,
0)
))

I have two reps that have earned bonus points: If I filter by rep the filter - the total seems to be fine - 

 

 

For Rep 1For Rep 1For Rep 1

For Rep 2For Rep 2For Rep 2

For both RepsFor both RepsFor both Reps When I show both reps the total is messed up.

In the chart It starts showing points that aren't really there. The bars with asterisks are the only ones that are "legit".
Bonus Points Chart.JPG


ANY help with this would be greatly appreciated! Like I said - this is a second attempt at this - just hoping to either see where I messed up in my previous attempts or see if I can get a new perspective on how to correct this issue. Thanks in advance!! 😄 



EDIT: I have also tried this: http://www.powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/.


Here is the script I use and I still have the same issue: 

Bonus Points PRO Customer = IF(COUNTROWS(VALUES('Customer'[Customer Name]))=1, [Bonus Points Measure], SUMX(VALUES(Customer[Customer Name]), [Bonus Points Measure]))

~heathernicoale
2 ACCEPTED SOLUTIONS
heathernicole
Continued Contributor
Continued Contributor

Woooohoooo! 😄 

 

Minions_high_five.jpg

 

I'm not sure why this worked - but I got it to work properly!! @Sean @kcantor   

Using a combination of what you all suggested in a previous post - all I did was change which column I was pointing to (referencing the customer) and it worked... I have no idea what the difference is. But I don't care at this point. 🙂 

 

Here's the final script and results!! 😄 
 
This: 

#Correct Script
Bonus Points PRO Customer = IF(COUNTROWS(VALUES('Customer'[ONLY Current Year Customers]))=1, [Bonus Points Measure], s
SUMX(VALUES(Customer[ONLY Current Year Customers]), [Bonus Points Measure]))

Replaced this: 

Bonus Points PRO Customer = IF(COUNTROWS(VALUES('Customer'[Customer Name]))=1, [Bonus Points Measure], SUMX(VALUES(Customer[Customer Name]), [Bonus Points Measure]))


And gave this :

 

 Correct Bonus Points.JPG

~heathernicoale

View solution in original post

Sean
Community Champion
Community Champion

@heathernicole EDIT: Already solved! Great! Smiley Happy I guess I was posting at about the same time! Smiley Happy

 

You can try this...

Measure2 Customer =
IF (
    ISFILTERED ( 'Customer'[Customer Name] ),
    [Bonus Points Measure],
    SUMX ( VALUES ( Customer[Customer Name] ), [Bonus Points Measure] )
)

 

Also try this...

Bonus Points PRO2 Customer =
IF (
    ISFILTERED ( 'SALES DETAILS'[Rep Code] ),
    [Bonus Points MEASURE],
    SUMX ( VALUES ( 'SALES DETAILS'[Customer] ), [Bonus Points MEASURE] )
)

 

 

As you can see all Measures I've given work... Fingers crossed... Smiley Happy

 

Bonus Points - Revisited.png

View solution in original post

3 REPLIES 3
Sean
Community Champion
Community Champion

@heathernicole EDIT: Already solved! Great! Smiley Happy I guess I was posting at about the same time! Smiley Happy

 

You can try this...

Measure2 Customer =
IF (
    ISFILTERED ( 'Customer'[Customer Name] ),
    [Bonus Points Measure],
    SUMX ( VALUES ( Customer[Customer Name] ), [Bonus Points Measure] )
)

 

Also try this...

Bonus Points PRO2 Customer =
IF (
    ISFILTERED ( 'SALES DETAILS'[Rep Code] ),
    [Bonus Points MEASURE],
    SUMX ( VALUES ( 'SALES DETAILS'[Customer] ), [Bonus Points MEASURE] )
)

 

 

As you can see all Measures I've given work... Fingers crossed... Smiley Happy

 

Bonus Points - Revisited.png

heathernicole
Continued Contributor
Continued Contributor

@Sean - lol Thanks so much for giving this another go! This solution looks great. I may make a backup copy of my report and give it a go. For now I'm leaving it so as not to break it. lol

 

THANK YOU!!!!Smiley Very Happy

~heathernicoale
heathernicole
Continued Contributor
Continued Contributor

Woooohoooo! 😄 

 

Minions_high_five.jpg

 

I'm not sure why this worked - but I got it to work properly!! @Sean @kcantor   

Using a combination of what you all suggested in a previous post - all I did was change which column I was pointing to (referencing the customer) and it worked... I have no idea what the difference is. But I don't care at this point. 🙂 

 

Here's the final script and results!! 😄 
 
This: 

#Correct Script
Bonus Points PRO Customer = IF(COUNTROWS(VALUES('Customer'[ONLY Current Year Customers]))=1, [Bonus Points Measure], s
SUMX(VALUES(Customer[ONLY Current Year Customers]), [Bonus Points Measure]))

Replaced this: 

Bonus Points PRO Customer = IF(COUNTROWS(VALUES('Customer'[Customer Name]))=1, [Bonus Points Measure], SUMX(VALUES(Customer[Customer Name]), [Bonus Points Measure]))


And gave this :

 

 Correct Bonus Points.JPG

~heathernicoale

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.