Filter Measure by unrelated table

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,379
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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,260
Wy don’t you join the product table to the fact table? That is how it is designed to work.
 

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,379
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.'.
 

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,379
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??
 

Watch MrExcel Video

Forum statistics

Threads
1,127,582
Messages
5,625,621
Members
416,124
Latest member
DeMoNloK

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
Top