Incorrect total

[Anonymous]

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?   

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

)

[PaulDBrown]

@Anonymous 

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

[Anonymous]

@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.  

VIR Table

Customer Name     Minimum Revenue        Incentive %

Customer A             500,000                                1.5%

Customer A             700,000                                2.5%

[PaulDBrown]

@Anonymous 

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.

[Anonymous · 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!

[Ashish_Mathur]

Hi,

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

[Anonymous]

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]

@Anonymous 

Try:

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

[Johanno]

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)

[Anonymous]

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]

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.