Slicer Not Working for Summary Table when using SUMMARIZECOLUMNS

[JST15]

Waterfall PBIX File 

Hello! I've created a waterfall chart that walks from Plan to Actuals using the variance between the two. My issue is that the slicer for my calendar filter is not working. I have four tables: 1) Calendar, 2) OE Account Names, 3)OE Actual Table and 4) OE Plan. Tables 3 & 4 contain numeric data and the OE Account Names is used to get Account Names from Account numbers in tables 3&4. I have relationships set between these four. To make the waterfall, I created a fifth table, "VarianceTable", using SUMMARIZECOLUMNS pulling fields from tables 2,3 & 4. I didn't want to include date (was hoping I didn't need to). The WaterfallChartAxisTable was created to orgainze the data into the fields I need for the waterfall categories. After putting the waterfall together and adding a Date slicer, nothing happens. I thought that SUMMARIZECOLUMNS respected table relationships so I wouldn't have to establish them in the "VarianceTable", but it is not working. 

Can someone help me identify the issue and how to fix? Thank you!!!!!

a link to the pbix file is provided.

[speedramps · Accepted solution]

Hi again

Click here for a SUMMARIZECOLUMNS example from Onedrive

Click here 

Please click thumbs up and the [accept solution] button

[speedramps]

You have created a new calcuated table called "VarianceTable" using  SUMMARIZECOLUMNS.

That table will build when you refresh  BEFORE you start using slicers on the visuls.

It has no date or relatiionship to the calendar table so it does not change when you change the slicers.

Consider creating a DAX measure which uses a var = a tempoary table with  SUMMARIZECOLUMNS..

Measure recalcuate each time you change the slicers.

Please click thumbs up and accept solution because I have answered this question,

Raise a new ticket with more details if you need help building the dax measure, many thnaks 😉

[JST15]

@speedramps  Thank you for replying! I'm not following you though. Are you saying to build this into the measure I'm using to create the VarianceTable? I'm just not sure how to set that up. Again, thank you!

VarianceTable =

VAR = a temporary table

SUMMARIZECOLUMNS(

        'OE Account Names'[GEN_07],

        "ActualAmount",[TotalActuals],

        "PlanAmount",[TotalPlan],

        "Variance",[Act - Plan Variance]

)