topic Re: Measure Total Incorrect When Filtering in DAX Commands and Tips

Measure Total Incorrect When Filtering

FrankieVee — Thu, 17 Jun 2021 13:54:27 GMT

I have a measure that currently multiplies two columns, each from its own table. The output works as desired, but when filtering to a particular company (using a slicer visual) the Total at the bottom does not change. I've attempted a few solutions I found from similar posts here, as well as other sites, but still no luck.

Here is the current table filtered to a single company (items vary). The Product Catalog Cost is my measure, as you can see from the incorrect total:

My measure:

Product Catalog Cost = SUMX(TableA,TableA[currentcost]) * (SUMX(TableB,TableB[agd_qty]))

Any help is appreciated!

Thank you, Frank V.

Re: Measure Total Incorrect When Filtering

AlB — Thu, 17 Jun 2021 15:21:45 GMT

Hi FrankieVee

Please show sample of TableA and TableB (relevant columns) and what exactly the desired measure should be calculating: the multiplication of which two columns? The values you show in the rightmost column in the table are exactly the same as those in the Agreement _Cost column

Are the two leftmost visible columns in the table visual measures? If so, show the code

Are TableA and TableB related? How?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: Measure Total Incorrect When Filtering

PaulOlding — Thu, 17 Jun 2021 15:31:26 GMT

I think it'll be hard to diagnose the issue without seeing the model.

One thing that does spring to mind is the summing of currentcost and agd_qty are happening separately in your measure, but it looks like currentcost can be zero.

At the total level this probably isn't working. Consider the row with agd_qty = 35. That 35 will be part of the sum of agd_qty and will be multiplied by the total of currentcost. Should the measure be multiplying agd_qty and currentcost per item first, then summing the result?

Re: Measure Total Incorrect When Filtering

FrankieVee — Tue, 22 Jun 2021 13:57:08 GMT

Hello, sorry for the late response!

I'm looking to multiple the "current cost" (found in TableA) by the quantity (found in TableB). The multiplication works as desired, my issue however is when filtering, the total at the bottom does not match. For example, in my OP's screenshot the total comes out to be 78,885.10, when it should be 1287.60.

The Agreement Cost is a column from the original table, and is not a measure. The only measure in my screenshot is the Product Catalog Cost.

The tables have a relationship between the Item ID.

Hope this helps!

Re: Measure Total Incorrect When Filtering

FrankieVee — Tue, 22 Jun 2021 14:04:12 GMT

This screenshot might be more relevant. I've taken out the Agreement Cost column (which isn't involved with my measure) and instead added the current cost column next to the quantity. Maybe this will help in understanding.

This also shows that the totals are still multiplying. My measure's total is the multiplication of the AGD_Qty and CurrentCost columns (as is in my measure's formula).

Re: Measure Total Incorrect When Filtering

AlB — Tue, 22 Jun 2021 15:39:30 GMT

FrankieVee

I'm assuming TableA[Company], TableA[ItemID] are the fields determining the filter context in your visual above:

Product Catalog Cost = SUMX ( SUMMARIZE( TableA, TableA[Company], TableA[ItemID] ), CALCULATE ( SUM ( TableA[currentcost] ) ) * CALCULATE ( SUM ( TableB[agd_qty] ) ) )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: Measure Total Incorrect When Filtering

FrankieVee — Tue, 22 Jun 2021 20:57:09 GMT

This did the trick! Thank you so much!