Forum Discussion

Ion_Ander's avatar
Ion_Ander
Icon for Helper I rankHelper I
5 years ago
Solved

summarize ignore slicer

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 ignor...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    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.

     

v-kkf-msft's avatar
v-kkf-msft
Icon for Community Support rankCommunity Support
5 years ago

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's avatar
Ion_Ander
Icon for Helper I rankHelper I
5 years ago

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-Q20dDk5RQsuoXR8QBR9u7OKWLb1uaSDncvv0YfA?e=reXxhM 

 

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's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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.