I’m trying to retrieve column totals using a virtual table with logic similar to what I applied to a physical table. My base data looks like this (Sheet1):
I’m trying to create a report that dynamically lists the average cost of items with a Condition value of 6 in a column like this:
I created the above example with a physical table as a stepping stone toward creating with a virtual table. But I’m stuck. Here’s how I created and used a physical table:
The problem is that this is merely a static solution that only works with a Year filter. I need to make this dynamic so that it works with any combination of my Year and Building slicers.
I thought that applying the similar logic through a virtual table would work, but this is where I’m stuck. I can accept that my entire approach may be off and perhaps the answer is simpler than I imagine. I am open to any approach that results in a report that dynamically lists the Average COST of all items displayed in each row of my report according to my slicer filters.
I’ve tried many variations of the following:
The problem is that this is merely a static solution that only works with a Year filter. I need to make this dynamic so that it works with any combination of my Year and Building slicers.
I thought that applying the similar logic through a virtual table would work, but this is where I’m stuck. I can accept that my entire approach may be off and perhaps the answer is simpler than I imagine. I am open to any approach that results in a report that dynamically lists the Average COST of all items displayed in each row of my report according to my slicer filters.
I’ve tried many variations of the following:
But all this does is return the same values I started with in each row:
How can I get the total value (the value in the card) populated into each row of my report?
I don’t think this is too difficult actually. Just requires understanding the context of the calculation based on the variety of selections that can be made.
I’m just working with my demo model here, but this should work for you I feel also.
So first calculate the average like you have done. Then create another formula that looks similar to this.
Average Total =
CALCULATE( [Average Sales], ALLEXCEPT( Sales, Dates[Year] ) )
Within the ALLEXCEPT part place in the columns you don’t want to remove filters from.
See here
And here
See how you go with this. Let me know if any further problems
Thank you! Definitely closer than I was- but what happens when you select all years? My example provides the right total, but the row values are off (I’ve been at DAX for about a month now - suppose I’m at the ‘I know just enough to be dangerous’ stage of learning)
hmm, interesting. I’m Excepting 3 filters, and I get wrong results if I unselect or multi-select any one of them. Anyway - ALLSELECT was the big answer for me, a Filter method I have not yet explored, but now makes total sense thanks to you( I was down a black hole trying to iterate through a virtual table!) Thanks again!
