Using a dimension to filter a measure

[Anonymous]

Hi everyone,

I am trying to slice a measure below based on the category .

The measure is Total Amount (works perfectly) and it calculates (Running total) "Total Amount" for an item and assigns it to the most recent category based on the Transdate (as filtered by the slicer) .

The dax is shown below:

----------------------------------------------

Total Amount =
VAR __cat = MAX( 'Table'[Category] )
VAR __summary =
CALCULATETABLE(
ADDCOLUMNS(
ADDCOLUMNS(
SUMMARIZE(
'Table',
'Table'[ITEMID],
'Table'[WarehouseID]
),
"@amt", CALCULATE( SUM( 'Table'[Amount] ) ),
"@maxdate", CALCULATE( MAX( 'Table'[TRANSDATE] ) )
),
"@Cat",
VAR __dt = [@maxdate]
RETURN
CALCULATE(
MAX( 'Table'[Category] ),
'Table'[TRANSDATE] = __dt
)
),
ALLSELECTED( 'Table'[Category] )
)
RETURN
IF(
__cat IN SELECTCOLUMNS( __summary, "_cat", [@Cat] ),
SUMX( FILTER( __summary, [@Cat] = __cat ), [@amt] )
)

However when i try to use a slicer (category) to interact with my measure  the value changes.

i have a dataset below :

https://www.dropbox.com/scl/fi/8cqz36yc44brmf2rzre7w/MATDW-SAMPLE-DATA_v2.xlsx?dl=0&rlkey=jzgswrfhet...

My current challenge is when i try to use the category as a filter , the results change to a wrong value . 

Pic 1: The results are perfect without the category filter :

Pic 2: Wrong values when i include the Category Filter . The values should remain the same as above.

Thanks for your help in advance ,it is appreciated.

@v-janeyg-msft 

[Anonymous]

Hi @Anonymous ,

Whether the advice given by @vanessafvg  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Hi Thanks for suggestion

below is dropbox link to the file

https://www.dropbox.com/s/cf6ho1p0hrxia3m/Final.pbix?dl=0

[vanessafvg]

I am still not 100% clear on what your intended behaviour is.  But what I can see in your total code is that you are combining categories in your total  As per the screenshot provided.  

I have add a new measure called total new to filter through the category to bring back what is shown in the table

If your intention is indeed to mix categories then filtering on A will remove the B from the equation so What is the purpose of filtering?

please see file attached. 

[vanessafvg]

will have a look at bit later!  

[Anonymous]

yes , but i dont see how to attach  a pbix file to this forum. it dosent allow me do that

[vanessafvg]

can you share from a dropbox or similar?  

[vanessafvg]

are you able to share your pbix rather than the dataset it would be easier to see how you have things set up