How can I retrieve a value from a virtual table and place in each row of report


#1

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):
image

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:

image

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:

image
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:
image

But all this does is return the same values I started with in each row:

image

How can I get the total value (the value in the card) populated into each row of my report?


#2

Hi Rod,

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

image

And here

image

See how you go with this. Let me know if any further problems

Thanks


#3

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)


#4

Actually - no worries, I think I’ve got it by counting years and an IF statement


#5

I believe it should still work here when selecting multiple or all years.

See below.

image

image

That’s the idea with the particular formula.

You can add your formula here if you want


#6

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!


#7

Ok cool. Yes certainly explore ALLEXCEPT. Plenty of use cases for scenarios like this one.


#8

Just so you can see what I’ve done:
AVECost6 = CALCULATE([AveReplacementCost6],ALLEXCEPT(Hierarch,Hierarch[Site],Hierarch[Year],Hierarch[Simulation]))

Works fine like this:

But not like this (Values reflect Year 1)


#9

Sorry for the delay on this one.

Seems quite odd to me. There must be something I missing as it should work as I expect.

For example here

image

This will all have to do with the context within the total.

Is it possible to see the model?


#10

I would be happy to share the model for your eyes only… How do I do that?


#11

Need to keep everything public with the forum, so will have to try work out the problem here

What does your current model look like and where are the columns and slicers coming from in those tables?

Chrs
Sam