Filtering DAX to another table using current row's value

Question

I have three tables, one a standard date table and two children tables, one budget and one forecast. I am using power pivot and would like to have a listing of the years / months across the top, and multiple brands going down the rows with the forecast and budget displayed in the grid. When I create the measure on the forecast table to sum the budget, it sums it correctly down to the month but when I view the pivot table, I get the summed value across all brands instead of filtering to the current row's brand:

    2017    
    October 
Row Labels  forecast    Budget
Brand A $950,000.15         ($3,884,649.00)
Brand B $3,500,000.00       ($3,884,649.00)
Brand C $1,650,000.00       ($3,884,649.00)
Brand D $980,000.00         ($3,884,649.00)
Brand E $100,000.00         ($3,884,649.00)
Brand F $183,465.00         ($3,884,649.00)
Grand Total $7,363,465.15   ($3,884,649.00)

My DAX formula looks like this:

Budget:=CALCULATE(Sum(Budget[SummedAmount]), values(Budget[Brand Code])= ForecastEntries[Brand Code])

How can I get the ~3,884,649 summing just over the current brand's brand code?

mendosi mendosi · Accepted Answer · 2017-10-09T23:20:31

The simplest way to achieve what you want is to add a dimension table Brands to the model which contains one row for each brand and then relate the Budget and Forecast tables to it on [Brand Code].

The hard way to do it is to set up your measure like this:

Budget:=CALCULATE(
            SUM(Budget[SummedAmount]), 
            FILTER(
                Budget, 
                Budget[Brand Code] = 
                    IF(HASONEVALUE(Forecast[Brand Code]), 
                       VALUES(Forecast[Brand Code]), 
                       Budget[Brand Code])
            )
         )

The second approach quickly becomes messy as there are more dimensions.

Filtering DAX to another table using current row's value

1 Answers