Dreaded Incorrect Measure Totals....

Question

I have been plagued by the infamous incorrect measure total problem, however, this time I can not find a way to resolve it. Below is a picture of the table, and then the measure formulas:

ItemDesc	Invoiced Qty	Invoiced Qty (Incorrect Site)	Invoiced Rank Incorrect	TOPN Incorrect Qty Switch	NEW Incorrect Invoiced Qty
MJ,ACC,BAG,6,TRANSITION,3/4X31/2,L/GLD	761	745	20	761	761
BEND,12,MJ,45,C153,CL	1721	736	21	1721	1721
BEND,6,MJ,90,C153,CL	1171	731	22	1171	1171
GLAND,6,MJ,C153,TC	1330	705	23	1330	1330
FLANGE,COMP,4,C110,FOR,DI,BARE-ND	640	640	24	640	640
BOX,VALVE,6850,562S,GI,ASSY,16T,24B	731	638	25	731	731
MJ,ACC,PACK,8,C153	1304	633	26	633	1304
BEND,12,MJ,45,C153,CL-ND	2560	623	27	623	2560
MJ,ACC,PACK,16,LESS,GLAND,3/4X41/2,B/N	972	596	28	596	972
MJ,ACC,PACK,12,LESS,GLAND,3/4X4,B/N	1275	554	29	554	1275
MJ,ACC,PACK,6,C153	1063	532	30	532	1063

[Invoiced Qty] = Sum(InvoicedQuantity)

[Invoiced Qty (Incorrect Site)] =

CALCULATE( [Invoiced Qty],

FILTER('fact SalesInvoiceView', RELATED('dim InventoryDimensionView'[InventSiteCode]) <> RELATED('Sell To Customer'[SellToCustomerCustomerSite])),

FILTER('fact SalesInvoiceView', RELATED('dim SalesInvoiceView'[DeliveryStateCode]) = RELATED('Sell To Customer'[SellToCustomerPrimaryStateCode]))

[Invoiced_Rank_Incorrect] =

VAR fx = RANKX( ALLSELECTED('dim ItemView'), [Invoiced Qty (Incorrect Site)])

RETURN

IF( ISINSCOPE('dim ItemView'[ItemDesc]), fx)

[TOPN Incorrect Qty Switch] =

VAR ranks = IF( ISINSCOPE('dim ItemView'[ItemDesc]), RANKX( ALLSELECTED('dim ItemView'), [Invoiced Qty (Incorrect Site)]))

VAR result = IF( ranks <= 25, SUM('fact SalesInvoiceView'[InvoicedQuantity]), [Invoiced Qty (Incorrect Site)])

RETURN

result

[NEW Incorrect Invoiced Qty] =

SUMX( VALUES( 'dim ItemView'[ItemDesc]), IF( [Incorrect Qty Switch] > 0, [Incorrect Qty Switch]) )

What I am trying to do, is rank the [Invoiced Qty (Incorrect Site)] ascending. Then, use the [TOPN Incorrect Qty Switch] to look at the top 25 items, and return the [Invoiced Qty], and return the [Invoiced Qty (Incorrect Site)] value for any rank > 25, with the correct summation.

Currently, the [TOPN Incorrect Qty Switch] has the correct values, but incorrect total, and the [New Incorrect Invoiced Qty] has the incorrect values (all showing [Invoiced Qty]), but the correct sum (this measure was created to try and get the [TOPN Incorrect Qty Switch] to sum correctly).

Note: The [ItemDesc] are in a separate table, related to the current table by Dim_Keys
Note: The yellow line in the picture is the 25th ranked item, and where the result measure should switch columns from [Invoiced Qty] to [Invoiced Qty (Incorrect Site)]

Please help! I have exhausted all option.

lbendlin · Answer

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data 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.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Forum Discussion

Dreaded Incorrect Measure Totals....

1 Reply

Related content

Incorrect turnover total

Measure Total Incorrect

Matrix Totals Incorrect

Incorrect total

Incorrect Total