summarize ignore slicer

[Ion_Ander]

Hi,
I have a measure that calculates the number of customers who lower purchases compared to the previous year.
But the problem is that i have slicer with the year in the report. so I need to ignore the year filter in the measure, but don´t know how.

This is my measure:

N_Clients_decrease= 
 CALCULATE(
    COUNTROWS(SUMMARIZE(CLI,CLI[CODCLI])),
    FILTER(SUMMARIZE(CLI,CLI[CODCLI]),[INCREASE]<0)
    )

The  year comes from another table (calendar Table) named DimDate that is related with CLI table by the date
*[INCREASE]=YearToDate-YearToDateLastYear

[amitchandak]

@Ion_Ander , Try like

countx(values(CLI[CODCLI]),if([INCREASE] <0, [CODCLI], blank()))

Hope your measure is correct

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

[Ion_Ander]

@amitchandak  Thanks for your replay, it still does not work as I hope ,

I have the same problem with your measure, i need to ignore the filter how came form a slicer with the year, thats becasuse if a client do not have sold in the year its considered as a decrease.

This slicer is filtering the sales table so that those customers without sales in the year are not taken into account. This is why i need to ignore the year filter.
In this snapshoot you can see the result is the same

and the reult mus be 934.

YTD=CALCULATE(sum(CLI[IMPNET]), DATESYTD(DimDate2[DateFull]))

Last YTD=CALCULATE ( [YTD], SAMEPERIODLASTYEAR ( 'DimDate2'[DateFull] ) )

One more time Thanks, its not what desired, but its more simple and probably more efficient.

[v-kkf-msft]

Hi @Ion_Ander ,

Try the following formula:

INCREASE = 
VAR CurrentYear = SUM(CLI[VALUE])
VAR Previous_Year = 
    CALCULATE(
        SUM(CLI[VALUE]),
        ALL(CLI),
        PREVIOUSYEAR(CLI[DATE]),
        GROUPBY( CLI, CLI[CODCLI] )
    )
RETURN CurrentYear - Previous_Year

N_Clients_decrease = 
CALCULATE(
    COUNTROWS(CLI),
    FILTER(CLI,[INCREASE]<0)
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Ion_Ander]

THANKS @v-kkf-msft but something not not working as expected, the number of decreeasing clients is to hight, i share the PBIX file to see.

https://a31069321-my.sharepoint.com/:u:/g/personal/ionander_gabyl_com/Eag3xi-Q20dDk5RQsuoXR8QBR9u7OK... 

What I expect is the result of the DECREASE SUMMARIZECOLUMN measure, which you can see in the attachment, but I cannot use this measure in a matrix, the summarizecolumn seems not compatible with the matrix.

[v-kkf-msft · Accepted solution]

Hi @Ion_Ander ,

I try another method, and the formula is:

DecreaseMeasure = 
VAR sel = SELECTEDVALUE(DimDate2[Year])
var emp = SELECTEDVALUE(EMPRES[NAME])
var tab = 
    CALCULATETABLE(
        SUMMARIZE(
            CLIENT,
            CLIENT[CODCLI],
            "LYTYTD", 
                CALCULATE(
                    SUM(CLIENT[IMPNET]),
                    FILTER(
                        ALL(CLIENT),
                        CLIENT[CODCLI] = MAX(CLIENT[CODCLI])
                        && CLIENT[EMPRESA] = emp
                        && YEAR(CLIENT[FECALB]) = sel-1
                    )
                ),
            "TYTD",
                CALCULATE(
                    SUM(CLIENT[IMPNET]),
                    FILTER(
                        ALL(CLIENT),
                        CLIENT[CODCLI] = MAX(CLIENT[CODCLI])
                        && CLIENT[EMPRESA] = emp
                        && YEAR(CLIENT[FECALB]) = sel
                    )
                )
        ),
        FILTER( ALL(CLIENT), YEAR(CLIENT[FECALB]) > 2019 && CLIENT[EMPRESA] = emp ),
        GROUPBY( DimDate2, DimDate2[QuarterFullName] )
    )
return COUNTROWS( FILTER( tab, [LYTYTD] > [TYTD] ) )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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