cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
texmexdragon
Power Participant
Power Participant

Incorrect total

Hello -  The measure below is returning the correct amounts at the row level, but not at the total value level.  Any advice on how to correct?   

 
texmexdragon_0-1620319474267.png

 

Internal Use scale accrual % =
SUM( Shipments[ShippedPrice] )
* MAXX (
FILTER (
VIR_Table,
VIR_Table[Customer] = MAX ( Shipments[Primary Customer2] )
&& SUM ( Shipments[ShippedPrice] ) > VIR_Table[Minimum Revenue]
),
VIR_Table[Incentive %]
)
1 ACCEPTED SOLUTION

@PaulDBrown    I tried this approach, and it works perfectly.    I referenced my original measure, in this measure: 

SUMX( VALUES( 'Date Table'[MonthnYear]), [Internal Use scale accrual] )
Thanks for the help!
Screenshot 2021-05-08 090737.jpg

View solution in original post

10 REPLIES 10
PaulDBrown
Super User II
Super User II

@texmexdragon 

If this proposed measure doesn't work,

With totals = SUMX(table, [Internal Use scale accrual %])

 

then try:

with totals = SUMX(SUMMARIZE(Table, Table [column 1], Table [Column 2], Table [column 3]..., "@total", 

SUM(Shipments[ShippedPrice])
* MAXX (
FILTER (
VIR_Table,
VIR_Table[Customer] = MAX ( Shipments[Primary Customer2] )
&& SUM( Shipments[ShippedPrice] ) > VIR_Table[Minimum Revenue]
),
.005 + VIR_Table[Incentive %]
)), [@total])
where the Table is the base table in your model/visual (depending on whether the fields are from Dim tables or the fact table) and the columns are those fields used in the visual.
sorry, to be of further help I need to see which fields are being used and the model setup for these tables




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown     Thanks for staying with me on this!    Your measure resulted in the same total value  (note on my "Internal Use" measure I have hidden the total), but it comes out to the same $95,661 as yours).   I literally had to show this to people today and did not want to confuse them by showing a total which is not correct.

The VIR table is populating my visual with just the Customer name (related to the shipments table).   

 

All of the other columns below are measures.   Month comes from the date table. 

 

The guts of the measure works perfectly fine...it is getting the correct row totals.   But what seems to be happening is that your measure, and mine, are grabbing the total value from the shipped revenue column and seeing if it is under/over the minimum revenue amount (VIR table), and then multiplying it times the Incentive %.     I do not want anything to be multiplied by the sum total.  Rather, I just want the column values to be added up.  So, in the example below, I need the "with totals" column to add up to $50,775.  

 

Screenshot 2021-05-07 182241.jpg

VIR Table

Customer Name     Minimum Revenue        Incentive %

Customer A             500,000                                1.5%

Customer A             700,000                                2.5%

 

@texmexdragon 

Not sure why the SUMX(SUMMARIZE)) measure isn't working. The measure firstly creates a virtual table with the relevante columns by SUMMARIZE and computes the SUM (your measure for each row). Then SUMX kicks in to add the values computed by your measure to calculate the total. 
might be worth trying to include your measure as a VAR (which will compute the row values in memory) and the use the SUMX(SUMMARIZE()) as the RETURN. 

Or try calculating you measure as a seperate measure and use that measure in the SUMX(SUMMARIZE()) measure.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown    I tried this approach, and it works perfectly.    I referenced my original measure, in this measure: 

SUMX( VALUES( 'Date Table'[MonthnYear]), [Internal Use scale accrual] )
Thanks for the help!
Screenshot 2021-05-08 090737.jpg

View solution in original post

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
texmexdragon
Power Participant
Power Participant

Hi @Johanno    I know what the issue is, just not sure how to fix it.   The issue is that the total is being summed up incorrectly.   I have tried using Sumx in replace of Sum.     If you look at my first post, what I am trying to acheive is the correct total of the values in the Internal Use column.    The reason why it is giving the current sum is because it is taking the sum of shipped price by the max % used in another column.   So, it is doing what the formula is telling it to do, I just don't know how to change it to something else...which would be to sum up the values in the Internal use column.  

 

Internal Use scale accrual =
SUM(Shipments[ShippedPrice])
* MAXX (
FILTER (
VIR_Table,
VIR_Table[Customer] = MAX ( Shipments[Primary Customer2] )
&& SUM( Shipments[ShippedPrice] ) > VIR_Table[Minimum Revenue]
),
.005 + VIR_Table[Incentive %]
)
PaulDBrown
Super User II
Super User II

@texmexdragon 

Try:

With totals = SUMX(table, [Internal Use scale accrual %])





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Johanno
Responsive Resident
Responsive Resident

It probably returns the correct value, just not the value you expect. 😊

 

You should debug the code and see what the total row returns for the different parts (like MAX ( Shipments[Primary Customer2] ) - is that the logical value for the total row?).

 

Using VARiables would make it easier to debug and return different parts.

 

This might lead to that you need a variation of the code to get you the expected result on the total row, then maybe you can distinguish that with somehting like:
IF( HASONEVALUE(whateveryoufilteron), ResultForAFilteredRow, ResultForATotalRow)

 

Thanks @Johanno    Normally I would agree but in this case, the values I need to see summed up are the exact ones showing on the rows.   The total is showing the sum of the aggrated shipped price * the max incentive.   I just need a sum that totals up exactly what is showing in those rows.  

Johanno
Responsive Resident
Responsive Resident

Ok, can you try to encapsulate the expression with SUMX ( the table you want to iterate with the column in your table, the expression). If this doesn't help I would be great with some sample data to work with.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors