Filter replacement inside matrix with visual filter

Power BI
1

I have a matrix displaying a sum of sales quantity and I have a slicer picking date periods.

I am displaying the sum of sales qty. I am also displaying the sum of sales for all dates (using ALL()) and that is working fine.

I am also attempting to show the sales quantity for date period 1 inside the matrix as a baseline and that is not working. I expected my stated filter context to replace the existing filter context, but it seems to be getting intersected. If date period 1 is in the slicer selection, it displays correctly, but if date period 1 is not, it is blank.

I hope I’m just doing something silly here, but if this can’t work for some reason, I would have to remove the visual filter and then “manually” apply the slicer to the salesqty. I tried that in the second matrix but couldn’t get that working either.

Appreciate any assistance. PBIX at https://www.dropbox.com/s/vgpvkq36k3uo9lg/baseline%20inside%20matrix%20with%20slicer.pbix?dl=0.

Thanks.

-jpr

2

Hello @jpr,

Thank You for posting your query onto the Forum.

As you pointed it out correctly, that there must be something silling happening here and yes it does.

So since you wanted to achieve the results for “Date Period 1” irrespective of the slicer selection. There will just be a minor tweak in the formula. Just remove the “FILTER” function from the formula and place it as “FILTER” condition inside the “CALCULATE” function directly and you’ll get the results. So this is how the formula will be -

SalesInPeriod1 - Harsh = 
CALCULATE( SUM( 'fact Sales2'[SalesQty] ) , 
    'dim DatePeriod'[DatePeriod] = 1 )

And now, if you make any selections into the slicer it’ll not affect the results and will continue to show the results for Date Period 1. Below is the screenshot of the results provided for the reference -

Results
Results894Ă—366 14.4 KB

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

baseline inside matrix with slicer - Harsh.pbix (33.5 KB)

1 Like
3

Thanks, Harsh. That was indeed a silly mistake that “looked right” to me due to lack of experience.
Thanks for your help.