Forum Discussion
Table visual with different filters for each column (slicer interaction involved)
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
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
2 Replies
- sturlaws
Resident Rockstar
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
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
