Missing Values when using Slicer, but row totals are correct

[TMLewis]

Hi all!

I've been wrestling with this matrix problem for a while. There's an interesting business requirement to have a table that has the following:

In the matrix visualization, there are several layers of drilldown, as follows: 

Category

Subcategory

Promotion Group

I am calculating Current Sales and Prior Sales for these 3 attributes. There are three slicers;  one for a "Quarter",  "Period", and Customer Name which all filter the matrix Prior Sales dollars. 

Current Sales is NOT impacted by the user selection of Quarter (forced to Current Quarter by my CALCULATE function, as seen in the below calculation)

Current Sales:

CALCULATE(SUM('Total Historical'[Sales $]),'Total Historical'[Category] = "Trade Cty" || 'Total Historical'[Category]= "Shopper Cty", 'Total Historical'[Quarter] = "Current Quarter"

The Sales are CORRECT in Subcategory, but some Promotion Group names are missing depending on "Quarter" or "Period" selected.  I do not want this. 

Here's what I'm seeing:

Category Name  |               Current Quarter Sales 

Coupons               -700K

     +Subcategory 1                -400K

             +Promo Group 1       -20K

             +Promo Group 2       -30K

             +Promo Group 3       -15K

      +Subcategory 2                -300K

             +Promo Group 4        -100K

             +Promo Group 5        -200K

Important information:

1. Category Sales and Subcategory sales are CORRECT.   But there are missing Promotion Groups in one Subcategory and the rollup doesn't make sense

2. The issue is seen in the Current Quarter Sales category when I select a historical quarter. If I select "Current Quarter" from the slicer, I see all Promotion Groups as expected, and the rollups all work correctly. 

Question:

There is no data model; there is only 1 table used, with current quarters and historical quarters. How can I force the matrix table to display ALL Promotion Groups, regardless of slicer quarter selected, but still display correct Current Sales?   

Thanks so much for your time - I'll be sure to grant kudos to the genius who solves it! 

[amitchandak]

@TMLewis ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[TMLewis]

https://1drv.ms/u/s!Av5L0aIROExnkjbEENtSaeVqwlai?e=f9rqrt

Thanks @amitchandak  - I can't directly link a file to the Forum (I'm guessing because I'm new?) but here's a PBIX file with the general issue.

When swapping between Quarter: "Current Version" and Quarter: P9, different promotions will appear. I want all of the promotions to appear, regardless of Quarter choice.

Thanks so much! 

TML

[v-kelly-msft · Accepted solution]

Hi @TMLewis ,

Create a dimension table as below:

Dimension Table = VALUES(Sheet1[Quarter])

Then create a measure as below:

_Prior Sales = FORMAT(DIVIDE(CALCULATE(SUM('Sheet1'[Sales]) ,FILTER(Sheet1,'Sheet1'[Quarter]=SELECTEDVALUE('Dimension Table'[Quarter]))),1000000),"0.0M")

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!