Helper I

2 years ago

Solved

Compare one category with the rest.

Hello,

I need help with dax for a Clustered column & Clustered Bar visuals to show comparison of one category with rest as others. I would like to create a clustered column visual with X-AXIS as year or mm-yy and y-axis as sum of count and one column as selected category and another column as rest categories. When no category selected then two columns should show ALL categories which will be equal columns of same count. For clustered bar visual, X axis has sum of count and y - axis has to be categories, in that categories ALL should be added as a category as a baseline to show. This visual should change based on year or MM-YY selection. Thank you.

Category	MM-YY	count
A	1/1/2018	15
A	2/1/2018	10
A	3/1/2018	20
A	4/1/2018	21
A	5/1/2018	22
A	6/1/2018	15
A	7/1/2018	16
A	8/1/2018	17
A	9/1/2018	18
A	10/1/2018	14
A	11/1/2018	12
A	12/1/2018	10
B	1/1/2018	15
B	2/1/2018	10
B	3/1/2018	20
B	4/1/2018	21
B	5/1/2018	22
B	6/1/2018	15
B	7/1/2018	16
B	8/1/2018	17
B	9/1/2018	18
B	10/1/2018	14
B	11/1/2018	12
B	12/1/2018	10
C	1/1/2018	10
C	2/1/2018	10
C	3/1/2018	10
C	4/1/2018	10
C	5/1/2018	10
C	6/1/2018	10
C	7/1/2018	10
C	8/1/2018	10
C	9/1/2018	10
C	10/1/2018	10
C	11/1/2018	10
C	12/1/2018	10
D	1/1/2018	15
D	2/1/2018	10
D	3/1/2018	20
D	4/1/2018	21
D	5/1/2018	22
D	6/1/2018	15
D	7/1/2018	16
D	8/1/2018	17
D	9/1/2018	18
D	10/1/2018	14
D	11/1/2018	12
D	12/1/2018	10

Need Help

Anonymous

2 years ago

Hi MandoPavs008

Just change the ALL measure:

ALL =
VAR _slicer =
    VALUES ( 'Table'[Category] )
VAR _currentmonth =
    MONTH ( MAX ( 'Table'[MM-YY] ) )
VAR _currentYear =
    YEAR ( MAX ( 'Table'[MM-YY] ) )
VAR _vtable =
    FILTER (
        ALL ( 'Table' ),
        FIND (
            'Table'[Category],
            CONCATENATEX ( VALUES ( 'Table'[Category] ), [Category] ),
            ,
            BLANK ()
        )
            = BLANK ()
    )
VAR _All =
    SUMX (
        FILTER (
            _vtable,
            MONTH ( [MM-YY] ) = _currentmonth
                && YEAR ( [MM-YY] ) = _currentYear
        ),
        [count]
    )
RETURN
    IF ( ISFILTERED ( 'Table'[Category] ) = FALSE (), SUM ( 'Table'[count] ), _All )

The result is as follow:

Best Regards

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

6 Replies

lbendlin
Super User
2 years ago
Your sample data is not suitable for your premise.
- MandoPavs008
  Helper I
  2 years ago
  Thank you for your response. The solution didn't work for my requirement.

Anonymous

Not applicable

2 years ago

Hi MandoPavs008

Please try this:

Here I create 2 measures:

ALL =
VAR _slicer =
    VALUES ( 'Table'[Category] )
VAR _currentmonth =
    MONTH ( MAX ( 'Table'[MM-YY] ) )
VAR _currentYear =
    YEAR ( MAX ( 'Table'[MM-YY] ) )
VAR _vtable =
    FILTER (
        ALL ( 'Table' ),
        FIND (
            'Table'[Category],
            CONCATENATEX ( VALUES ( 'Table'[Category] ), [Category] ),
            ,
            BLANK ()
        )
            = BLANK ()
    )
RETURN
    SUMX (
        FILTER (
            _vtable,
            MONTH ( [MM-YY] ) = _currentmonth
                && YEAR ( [MM-YY] ) = _currentYear
        ),
        [count]
    )

Selected =
VAR _slicer =
    VALUES ( 'Table'[Category] )
VAR _currentmonth =
    MONTH ( MAX ( 'Table'[MM-YY] ) )
VAR _currentYear =
    YEAR ( MAX ( 'Table'[MM-YY] ) )
VAR _vtable =
    FILTER (
        ALL ( 'Table' ),
        FIND (
            'Table'[Category],
            CONCATENATEX ( VALUES ( 'Table'[Category] ), [Category] ),
            ,
            BLANK ()
        )
            <> BLANK ()
    )
RETURN
    SUMX (
        FILTER (
            _vtable,
            MONTH ( [MM-YY] ) = _currentmonth
                && YEAR ( [MM-YY] ) = _currentYear
        ),
        [count]
    )

Then add a Clustered column chart:

and a Clustered bar chart:

Next add 2 slicer:

Select the Category and click the Edit interaction in the Format pane:

And click the button of the Category chart:

The same to MM-YY Slicer and Clustered column chart:

The result is as follow:

Best Regards

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

Compare one category with the rest..pbix45 KB

MandoPavs008
Helper I
2 years ago
Thank you for the solution. I got the bar chart but the column measure which i created is not working clearly. I would like to create the clustered column visual with Slicer for categories as A, B, C, D. When i select a Category "A' then it should compare with the rest so one column has selected "A" and another column has total count for rest of the category as one column. If no category selected then two columns should show same count as ALL. I want to have one slicer for category instead of two. Can you please look in to it?
- Anonymous
  Not applicable
  2 years ago
  Hi MandoPavs008
  
  Just change the ALL measure:
  
  ALL = VAR _slicer = VALUES ( 'Table'[Category] ) VAR _currentmonth = MONTH ( MAX ( 'Table'[MM-YY] ) ) VAR _currentYear = YEAR ( MAX ( 'Table'[MM-YY] ) ) VAR _vtable = FILTER ( ALL ( 'Table' ), FIND ( 'Table'[Category], CONCATENATEX ( VALUES ( 'Table'[Category] ), [Category] ), , BLANK () ) = BLANK () ) VAR _All = SUMX ( FILTER ( _vtable, MONTH ( [MM-YY] ) = _currentmonth && YEAR ( [MM-YY] ) = _currentYear ), [count] ) RETURN IF ( ISFILTERED ( 'Table'[Category] ) = FALSE (), SUM ( 'Table'[count] ), _All )
  
  The result is as follow:
  
  Best Regards
  
  Zhengdong Xu
  If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

MandoPavs008
Helper I
2 years ago
Thank you for your help. Appreciate it

Forum Discussion

Compare one category with the rest.