Filter the contents inside MATRIX visual

[Thor2022]

Dear All 

I have a matric that contains the following information: 

- Rows: Warehouse names: A, B and C 

- Columns: Years 

- Values: Profits 

and I have a multiselection slicer that contains the Warehouse names. 

I want to be able to compare the chosen values in the slicer and see the result in the Matrix in a way that the MATRIX will only show comparison if they are different, but if they are identical it will show blank, this is an example 

Thanks in advance 

Regards 

[pi_eye]

Hi Thor

Can you elaborate on the example? So for example if the column values for year #2 are 5,6 and 7, it shows but if it is 3,3, and 3  it is blank?

See example below:

Try this expresion in DAX:

Show diff Values = If(Calculate(DISTINCTCOUNT(WareHouse[Value]),ALLSELECTED(WareHouse[WareHouse]))=1,"", Sum(WareHouse[Value]))

What it is doing:

Calculate(DISTINCTCOUNT(WareHouse[Value]),ALLSELECTED(WareHouse[WareHouse])) --> Gives you the total of distinct values per warehouse

If(....... =1,"",...) ---> tests for the distinct values =1, thie means they are all the same. Returns blank if that is the case, and the sum if they are different.

Thanks,

Pi

[Thor2022]

Hi Pi

Thanks a lot, I will try it on Monday. 

Until then, I just need to add that the profit value is a measure, not a column .. so I am not sure if this DAX can be applied? 

Thanks a gain 

Regards 

[pi_eye]

Hi @Thor2022 

This is interesting, because for some reason it's not as simple to aggregate a distinct count on another measure! However it is always possible in DAX 😉

I created a new measure rather than chain it in called "New count" which evaluates to the total number of distinct sums. I am use group by to create an aggregated table which I can pass the summed column through to a  distinct count.

New Count = calculate(

        countx(

            distinct(SELECTCOLUMNS( GROUPBY(WareHouse,WareHouse[WareHouse],"SumVal",sumx(CURRENTGROUP(),WareHouse[Value])),[SumVal]) ) ,

            [SumVal] ),

        ALLSELECTED(WareHouse[WareHouse])

)

On it's own, it looks like this: (sorry I can't seem to cut and paste images right now)

	1	2	3	4	5	6	7	8
WH1	4	2	1	2	3	3	3	3
WH2	4	2	1	2	3	3	3	3
WH3	4	2	1	2	3	3	3	3
WH4	4	2	1	2	3	3	3	3

Added into the final expression gives you: 

Show diff Values = If([New Count]=1,"", Sum(WareHouse[Value]))

Let me know if this works,

Pi

[PaulDBrown]

Here is one way. Basically you compare the sum (profit) of each warehouse with the average for all warehouses in a year.

First the model:

 The calculation (without totals)

No duplicate profits =
VAR _Profit =
    SUM ( fTable[Profit] )
VAR _Average =
    CALCULATE ( AVERAGE ( fTable[Profit] ), ALL ( 'Warehouse table'[Warehouse] ) )
RETURN
    IF (
        AND (
            ISINSCOPE ( 'Warehouse table'[Warehouse] ),
            ISINSCOPE ( 'Year Table'[Year] )
        ),
        IF ( _Profit = _Average, BLANK (), _Profit )
    )

and if you need totals:

No Dups with totals = 
SUMX(SUMMARIZE(fTable, 'Warehouse table'[Warehouse], 'Year Table'[Year]), [No duplicate profits])

Sample PBIX attached

[Thor2022]

Hi Paul 

Thank you for your reply .

I find the logic in your solution excellent but as I mentioned before, the Profit value is a measured value that can't be calculated in the average function !!! 
looking forward to your reply 

REgards 

[PaulDBrown]

My apologies. I did see you mention it and then promptly forgot about it in the solution.

This measure will work:

No duplicate profits =
VAR _Profit = [Your measure]
VAR _Average =
    AVERAGEX ( ALL ( 'Warehouse table'[Warehouse] ), [Your measure] )
RETURN
    IF (
        AND (
            ISINSCOPE ( 'Warehouse table'[Warehouse] ),
            ISINSCOPE ( 'Year Table'[Year] )
        ),
        IF ( _Profit = _Average, BLANK (), _Profit )
    )

[PaulDBrown · Accepted solution]

@Thor2022 

Actually, thinking about it, the average method could potentially not achieve what you are looking for.

If you have a set of values for a year like {30, 29, 30, 31}, the average method will hide both 30 values.

The following  method however will work: compare the minimum value in a set to the maximum value. If they are the same, all values are the same...

So...

No duplicate profits =
VAR _Min =
    MINX ( ALL ( 'Warehouse table'[Warehouse] ), [Your measure] )
VAR _MAX =
    MAXX ( ALL ( 'Warehouse table'[Warehouse] ), [Your measure] )
RETURN
    IF (
        AND (
            ISINSCOPE ( 'Warehouse table'[Warehouse] ),
            ISINSCOPE ( 'Year Table'[Year] )
        ),
        IF ( _Min = _Max, BLANK (), [Your measure] )
    )

PS. I tried deleting the previous message but the forum won't let me for that particular post for some reason....

[Thor2022]

Great work 

Highly appricated 

[pi_eye]

Hi @Thor2022 

This is interesting, because for some reason it's not as simple to aggregate a distinct count on another measure! However it is always possible in DAX 😉

I created a new measure rather than chain it in called "New count" which evaluates to the total number of distinct sums. I am use group by to create an aggregated table which I can pass the summed column through to a  distinct count.

New Count = calculate(

        countx(

            distinct(SELECTCOLUMNS( GROUPBY(WareHouse,WareHouse[WareHouse],"SumVal",sumx(CURRENTGROUP(),WareHouse[Value])),[SumVal]) ) ,

            [SumVal] ),

        ALLSELECTED(WareHouse[WareHouse])

)

On it's own, it looks like this: (sorry I can't seem to cut and paste images right now)

	1	2	3	4	5	6	7	8
WH1	4	2	1	2	3	3	3	3
WH2	4	2	1	2	3	3	3	3
WH3	4	2	1	2	3	3	3	3
WH4	4	2	1	2	3	3	3	3

Added into the final expression gives you: 

Show diff Values = If([New Count]=1,"", Sum(WareHouse[Value]))

Let me know if this works,

Pi

[Thor2022]

Hi Pi 

I am sure its a great solution , but give me some time to digest it , since some of the used functions are new for me 🙂 

Regards