Using non-explicit Filters in Measures?

[mdannemiller]

I need to compare a calculated value to the same calculated value where 2 fields are matched.

for example, if I'm looking at bus fare I'd have a table like this:

[Fare paid], [Route], [City], [State], [riderID] 

So let's say that for route 1, the sum of all the fares paid is $10,000.  I can use a simple measure Calculate(SUM([Fare paid]) to find it.  However, I then need to compare that total to the fares paid for all routes in the same city and state.  So.  Let's say for Austin, TX route 1, the total is 100,000.

I'm having huge issues making this work.  if I use CALCULATE(SUM([fare paid], ALLEXCEPT('Bus Fare Fact', [City], [State])) I get the sum for everything.  I'd want to do a filter like this CALCULATE(SUM([Fare paid]), [City] = [City], [State] = [State])) But that obviously doesn't work.  Is there a way that I can access that value with a measure?  I tried with a calculated column where I know I can reference the column, but, because I'm using this calculation multiple times, it was taking 5-10 minutes to render the page, so I need to use a measure for performance issues.  

I also can't use a separate table to do the calculations (at least a non-memory table), because I have a rider table with demographic information about the rider that is used in filters in this table.

[v-danhe-msft]

Hi @mdannemiller,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[v-danhe-msft]

Hi @mdannemiller,

Based on my test, you could refer to below steps:

Sample data:

Create a measure:

Filter value = CALCULATE(SUM(Table1[Fare paid]),FILTER(ALL('Table1'),'Table1'[State]=MAX('Table1'[State])), FILTER(ALL('Table1'),'Table1'[City]=MAX('Table1'[City])))

Result:

If I misunderstand you, could you please share some sample data and post your desired result if possible?

Also, you could download my pbix file to have a view.

Regards,

Daniel He