Filter Measure by unrelated table

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,386
Hi Boardies,

So I've finally got the opportunity to mess around with DAX and some real-world data.

I have a FACT table.

DATE | SITE | PRODUCT | NET SALES

and a Dimension Table that only contains unique values (These two tables are not related)

PRODUCT

I also have the following Measure TotalNetSales:=SUM(FACT[NET SALES])

The PRODUCT dimension table is on a worksheet (it is in the Data Model) and I'm hoping I can use it as a filter for a measure.

I have tried this FilteredNetSales:=CALCULATE([TotalNetSales], FILTER(FACT, FACT[Summary] = PRODUCT[Summary]))

But this is wrong, I think because the FILTER is expecting one to evaluate the expression rather than a list???

My pivot has DATE and SITE as row headers.

I'm displaying total sales and want to also display filtered Sales so that I can create a percentage of Sales column.

Any help would be appreciated.

/Comfy
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
I tried that but may have/probably made a mistake as it did not give the result that I was expecting.

My end goal is to have 6+ dimension tables that filter 6+ measures.

If I add a relation between the Dimension table [Summary] and the Fact table [Summary] and the measure being:

=CALCULATE([TotalNetSales], FILTER(FACT, 'FACT'[Summary] = DIMENSION[Summary]))

I get the following error:

This formula is invalid or incomplete: 'Calculation error in measure 'FACT'[FilteredSales]: A single value for column 'Summary' in table 'DIMENSION' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.'.
 
Upvote 0
If I hard code the filter values I get the required result:

=CALCULATE([TotalNetSales], FILTER('FACT', 'FACT'[Summary] = "ProductA" || 'FACT'[Summary] = "ProductB" || 'FACT'[Summary] = "ProductC" || 'FACT'[Summary] = "ProductD" || 'FACT'[Summary] = "ProductE"))

Is it possible to provide a list of values??
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top