Table visual with different filters for each column (slicer interaction involved)

[ANAC014]

Hello dear community! please I need your support.

I have the following table

Sales Person	Amount	Week	Quarter
Anna	10	25	Q2
Peter	20	25	Q2
Mary	30	25	Q2
Anna	40	26	Q2
Peter	50	26	Q2
Mary	60	26	Q2
Anna	70	27	Q3
Peter	80	27	Q3
Mary	90	27	Q3
Anna	100	28	Q3
Peter	110	28	Q3
Mary	120	28	Q3

Then, I need to create following table with following info:

Sales Person	Week	This Quarter (Q3)	Previous Quarter (Q2)
Anna	10	170	50
Peter	20	210	90
Mary	30	190	70

The problem is that I have a slicer for Week, so the colum week is filtered ok with the slicer... but the other two colums, which should sum al the values corresponding to this quarter or previous quarter doesnt work because the slicer.

Could you please give me a hand?

@Anonymous 

[sturlaws · Accepted solution]

Hi,

based on your sample data, you could do this:

Current quater =
VAR _cq =
    CALCULATE ( MAX ( 'Table'[QuaterNo] ); ALL ( 'Table' ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] );
        FILTER ( ALLEXCEPT ( 'Table'; 'Table'[Sales Person] ); 'Table'[QuaterNo] = _cq )
    )

Previous quater =
VAR _cq =
    CALCULATE ( MAX ( 'Table'[QuaterNo] ); ALL ( 'Table' ) ) - 1
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] );
        FILTER ( ALLEXCEPT ( 'Table'; 'Table'[Sales Person] ); 'Table'[QuaterNo] = _cq )
    )

The idea is to find the last quater of you dataset, and then remove all filters except the filters on sales person. I guess your sample dataset is a bit simplified. I guess you have date table in your report/model, so you can make use of the TODAY()-function to identify the current quater. If your data spans several year, you can create a quaterYearNum-column like this ([year]-2010)*4+quaterNumber(2010 is chosen somewhat arbitrarily). This will give you a sequence of quaters, and the previous quater can be refererenced by curretn quaterYearNum-1. In order to get quater number from a date, you can use the format-function: FORMAT(date,"Q").

cheers,

s
 

[ANAC014]

Works perfectly my friend, thanks so much!

---

---

@sturlaws wrote:

Hi,

based on your sample data, you could do this:

Current quater =
VAR _cq =
    CALCULATE ( MAX ( 'Table'[QuaterNo] ); ALL ( 'Table' ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] );
        FILTER ( ALLEXCEPT ( 'Table'; 'Table'[Sales Person] ); 'Table'[QuaterNo] = _cq )
    )

Previous quater =
VAR _cq =
    CALCULATE ( MAX ( 'Table'[QuaterNo] ); ALL ( 'Table' ) ) - 1
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] );
        FILTER ( ALLEXCEPT ( 'Table'; 'Table'[Sales Person] ); 'Table'[QuaterNo] = _cq )
    )

The idea is to find the last quater of you dataset, and then remove all filters except the filters on sales person. I guess your sample dataset is a bit simplified. I guess you have date table in your report/model, so you can make use of the TODAY()-function to identify the current quater. If your data spans several year, you can create a quaterYearNum-column like this ([year]-2010)*4+quaterNumber(2010 is chosen somewhat arbitrarily). This will give you a sequence of quaters, and the previous quater can be refererenced by curretn quaterYearNum-1. In order to get quater number from a date, you can use the format-function: FORMAT(date,"Q").

cheers,

s
 

---