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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
rcb0325
Helper I
Helper I

Measures not totaling properly! I have tried all I know how to do.

Originally posted in "DAX Commands", but wanted to try "Desktop" since I seem to have better luck here. 

 

 

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: 

 

ItemDescInvoiced QtyInvoiced Qty (Incorrect Site)

Invoiced Rank

Incorrect

TOPN Incorrect Qty SwitchNEW Incorrect Invoiced Qty
MJ,ACC,BAG,6,TRANSITION,3/4X31/2,L/GLD76174520761761
BEND,12,MJ,45,C153,CL17217362117211721
BEND,6,MJ,90,C153,CL11717312211711171
GLAND,6,MJ,C153,TC13307052313301330
FLANGE,COMP,4,C110,FOR,DI,BARE-ND64064024640640
BOX,VALVE,6850,562S,GI,ASSY,16T,24B73163825731731
MJ,ACC,PACK,8,C1531304633266331304
BEND,12,MJ,45,C153,CL-ND2560623276232560
MJ,ACC,PACK,16,LESS,GLAND,3/4X41/2,B/N97259628596972
MJ,ACC,PACK,12,LESS,GLAND,3/4X4,B/N1275554295541275
MJ,ACC,PACK,6,C1531063532305321063

 

 

[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 = RANKXALLSELECTED('dim ItemView'), [Invoiced Qty (Incorrect Site)])
RETURN
IFISINSCOPE('dim ItemView'[ItemDesc]), fx)
 
[TOPN Incorrect Qty Switch] =
VAR ranks = IFISINSCOPE('dim ItemView'[ItemDesc]), RANKXALLSELECTED('dim ItemView'), [Invoiced Qty (Incorrect Site)]))
VAR result = IFranks <= 25SUM('fact SalesInvoiceView'[InvoicedQuantity]), [Invoiced Qty (Incorrect Site)])
RETURN
result
 
[NEW Incorrect Invoiced Qty] =
SUMXVALUES'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. 

3 REPLIES 3
lbendlin
Super User
Super User

Not clear what is sample data and what is part of your attempts.  Can you please elaborate?

The table shows some of the results for the defined measures. Each measure references the measure before it. the last measure, NEW Incorrect Invoiced Qty, is not totalling correctly. 

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

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.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.