Slicer not filtering on Measure

[GrahamR99]

Hello

I have a Slicer that filters on Area, it works fine on my matrix, but when I put a Measure on my matrix I get the shops that my slicer is filtering and I also get all the other shops with the measure at 100%.

Why does my slicer not filter on the Measure?

Here is the code for the Measure.

P_Year_Difference = CALCULATE(DIVIDE(SUM(CT_Query[Total_Gross_Paid]), Sum(CT_Query[CT_Query_LastYear.Total_Gross_Paid]) )-1)

Regards

GrahamR99

[amitchandak]

I doubt join is missing between tables.

[GrahamR99]

Hello amitchandak

What is a doubt join?

There is no relationship between the two tables as I have a thrid table which is a list of shops which has a relationship to both tables and blocks me from creating a relationship between the two tables.

The only line is a merge and that is a full outer join.

Regards

GrahamR99       

[v-xuding-msft · Accepted solution]

Hi @GrahamR99 ,

It is caused by minus 1 while calculating increased percentage. Please try to drag the column "Shops" which is from the table "Shops" into the table.

Please see the attachment below.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GrahamR99]

Hello Xue Ding

Thank you for your help the minus 1 pointed me in the right direction.

I was able to use a simlar formual as a column instead of a measure.

Regards

GrahamR99

[GrahamR99]

Hello Xue Ding

When I delete the -1 from the measure formual the filter works.

What is the -1 doing to stop the filter from working?

However I need a matrix with sales this year, sales last year and the percentage differance.  Now I have over 100% for my percentage difference.

Regards

GrahamR99 

[v-xuding-msft]

Hi @GrahamR99 ,

You could change the formula from a/b-1 to (a-b)/b like below.

Measure =
VAR a_minus_b =
    SUM ( CT_Query[Total_Gross_Paid] )
        - SUM ( CT_Query[CT_Query_LastYear.Total_Gross_Paid] )
RETURN
    DIVIDE ( a_minus_b, SUM ( CT_Query[CT_Query_LastYear.Total_Gross_Paid] ) )

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.