BI Power Filters with two data sets.

[FormworkFan]

I have simple report that is using two seprate Excel Files for the data. One Files has Sales Goals for each region and individual Market Segements.

The other File is the YTD Sales seperated by Region and the Market Segment.  I created a Many to Many Relationship between these tables via the Region. I'v created a basic Table for this info using two Slicers.  1 Slicer for the Region and One for the Market Segment.

This seems to be working only for the YTD Sales values. When I remove or add the Market Segments, the 2023 Goals Value does not change. See Screen shots below.  You can see from the Data, Goal for the selected Region and Market Segment should be coming up as $762K, but instead I'm getting the Total of a 3 Market Segements for that Region.  To me this seems very basic, but I'm not sure why the Sicers can handle this to product the result needed.

any Feed back would be appreciated!

[Ashish_Mathur · Accepted solution]

Hi,

Do not create a many to many relationship.  Create 2 Dim Tables - Region and Market Segment - each table should have a single column with unique entries.  Create a Many to One relationship from the 2 Fact  tables to the 2 Dim Tables.  To your visuals/filters/slicers, drag Region and Market Segment from the 2 Dim Tables.

Does this help? 

[FormworkFan]

I'm not sure I understand.  My Two Data Sets are very basic and the only option I can active is a Many to Many relatiohip based on the Regions.  These Tables are going to be coming from two seperate Sources and I don't think Merging or appending the tables will work.

[Ashish_Mathur]

Hi,

Please read my previous post again.

[FormworkFan]

I'm not farmilar with dim Talbes. I have been trying this out in Power Q, but with not much luck.

I do appreicate the feed back

[Dangar332]

hi, @FormworkFan 

i think you don't add market segment column to your visuals .

so add market segment column to your visuals.

[FormworkFan]

Adding the Market Segment Column does not work. See below.  The Highlight Row is what I'm looking to get based on the Section of the two slicers. The bottom two rows have YTD Sales info of $5,500 & $10,000 that is coming from the Northeast Region, but from other Market Segments.

The Logic to me, is not working.

[FormworkFan]

Adding the Market Segment into the table doee not really help or accomplish what I'm trying to do.

Ideally when I have my slicers are shown, there should only be 1 line for Northeast - Commercial wiht a goal of $762K and YTD Sale of $4000.