Filter matrix colum

[Anonymous]

Hi All,

Would like to filter a Colum in a Matrix

I have 3 Fileds O.Number, Size and Sale %, and would like to filter (Colum 2) Size - 1.L in matrix to display values between range >0% <=40%.

I tried with Matrix any other chart /Format to achieve the below result.

.Number Size Sale %
ML00939-015 1.M 8.64%
FL00939-016 4.XXL -100.00%
FL00939-017 1.L 0.00%
FL00939-018 1.L -3.47%
FL00939-019 3.XL -21.44%
ML00939-015 1.M 4.69%
FL00939-021 3.XL -7.41%
FL00939-022 4.XXL -100.00%
ML00939-015 1.M 18.33%
ML00939-015 1.M 15.12%
FL00939-025 1.L -1.59%
ML00939-015 1.M 7.15%
FL00939-027 1.L -7.80%
ML00939-015 1.M 4.45%
FL00939-029 1.L -0.63%
FL00939-030 3.XL -3.77%
FL00939-031 4.XXL -100.00%
ML00939-015 1.M 1.87%
FL00939-033 3.XL -6.70%
ML00939-015 1.M 28.52%
FL00939-035 1.L 0.00%
ML00939-015 1.M 7.53%
ML00939-015 1.M 18.81%
ML00939-015 1.M 25.20%
FL00939-039 3.XL -56.53%
ML00939-015 1.M 11.60%
FL00939-041 4.XXL -100.00%
ML00939-015 1.M 12.33%
ML00939-015 1.M 15.32%
LL00939-015 3.XL -16.96%
LL00939-016 4.XXL -100.00%
LL00939-017 1.M 14.67%
LL00939-018 1.M 8.82%
LL00939-019 3.XL -2.62%
LL00939-020 1.M 4.51%
LL00939-021 1.L -2.65%
LL00939-022 1.L -23.20%
LL00939-023 1.M 104.94%
LL00939-024 1.M 51.02%
LL00939-025 1.M 35.25%
LL00939-026 3.XL -15.08%
LL00939-027 4.XXL -100.00%
LL00939-028 1.M 35.25%
LL00939-029 3.XL -15.08%
LL00939-030 4.XXL -100.00%
LL00939-031 1.M 35.25%
LL00939-032 3.XL -15.08%
LL00939-033 4.XXL -100.00%
LL00939-034 1.M 14.57%
LL00939-035 1.L -4.16%
LL00939-036 4.XXL -100.00%
LL00939-037 1.L -6.87%
LL00939-038 1.L -6.87%
LL00939-039 1.M 32.74%
LL00939-040 1.M 8.27%
LL00939-041 1.L -9.45%
LL00939-042 3.XL -45.69%
LL00939-043 4.XXL -100.00%
ML00939-015 1.M 158.68%
FL00939-016 1.L 0.00%
FL00939-017 3.XL -3.05%
FL00939-018 1.M 29.91%
FL00939-019 1.L -1.80%
ML00939-015 1.M 14.34%
FL00939-021 1.M 10.00%
FL00939-022 1.M 9.62%
ML00939-015 1.M 38.31%
ML00939-015 1.L 0.11%
FL00939-025 3.XL -22.16%
ML00939-015 1.M 14.10%
FL00939-027 4.XXL -100.00%
ML00939-015 1.M 2.99%
FL00939-029 1.L -1.56%
FL00939-030 4.XXL -100.00%
FL00939-031 1.M 60.22%
ML00939-015 1.L -2.31%
FL00939-033 3.XL -16.92%
ML00939-015 1.L 0.00%
FL00939-035 1.L -7.39%
ML00939-015 1.M 36.64%
ML00939-015 1.L 0.00%
ML00939-015 1.L -0.04%
FL00939-039 1.M 39.01%
ML00939-015 1.L -2.75%
FL00939-041 1.M 24.68%
ML00939-015 3.XL -8.70%
ML00939-015 4.XXL -100.00%
ML00939-015 1.L -0.45%
FL00939-016 1.M 20.04%
LL00939-067 3.XL -14.11%
ML00939-058 4.XXL -100.00%
FL00939-019 1.L -2.45%
ML00939-015 1.L -5.04%
FL00939-021 1.M 1.05%
FL00939-022 3.XL -22.26%
ML00939-015 1.L 0.00%
ML00939-015 3.XL -8.12%
XL00939-077 1.L -0.10%
ML00939-015 1.M 5.34%
FL00939-087 1.L 0.00%
ML00939-015 1.M 46.28%
ML00939-016 1.L -4.57%
FL00939-030 3.XL -7.03%
FL00939-031 4.XXL -100.00%
ML00939-015 1.L -15.84%
FL00939-033 1.L -0.59%
ML00939-015 1.M 87.91%
XL00939-088 1.M 16.25%
ML00939-015 1.M 17.55%
ML00939-015 4.XXL -100.00%
ML00939-015 1.M 43.32%
FL00939-039 1.M 30.57%
ML00939-015 1.L -10.92%
FL00939-041 1.M 30.57%
ML00939-015 1.M 20.17%
ML00939-015 1.L -16.30%
ML00939-016 4.XXL -100.00%
FL00939-017 1.M 96.73%
LL00939-068 3.XL -31.54%
ML00939-059 1.M 12.60%
FL00939-020 1.M 25.00%
ML00939-016 1.L 0.00%
FL00939-023 1.L -2.01%
FL00939-024 1.M 15.15%
ML00939-015 3.XL -27.53%
ML00939-015 1.L -0.37%
XL00939-078 1.M 19.38%
ML00939-016 1.M 30.30%
FL00939-088 4.XXL -100.00%

Regards,

Samuel

[v-xicai]

Hi @Anonymous ,

You can create measure Filter1 like DAX below, then put the Filter1 in the Visual Level Filter of Matrix visual , setting Filter1 as "is not blank".

Filter1 =
var d=CALCULATE(MAX(Table1[Sale %]),FILTER(ALLSELECTED(Table1), Table1[Size] ="1.L"))
return
IF(d>0 &&d<=0.4, 1, BLANK())

Best Regards,

Amy

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

[Anonymous]

Hi Amy,

The Colum 1.L still reflects Blank matrix  column after applying the visual filter

Regards,

Samuel

[sturlaws]

when I filter your dataset for L1 and sales between 0 and 0.4, I get this:

so for your matrix, only these O.numbers should be visible?

[Anonymous]

Hi Sturlaws,

Can you share the pbix file.

 Regards,

Sam

[sturlaws]

It's only the data you provided, filtered in Power Query on Size = 1.L, and sales between 0 and 0.4

[Anonymous]

Tried in mutiple ways but result remains same and  does not work/filter  in the matrix chart

[sturlaws]

Sorry, what I meant to ask, is it only these O.Numbers which should be visible in your matrix visual?

[Anonymous]

Matrix should as is but the values of 1.L(Colum 2) has to filtered within a range >0 & <=0.4

[v-xicai]

Hi  @Anonymous ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[sturlaws]

You can use this measure to only show the O.Numbers where 1.L and the sum of Sale between 0 and 0,4, add it to the filter of a visual and filter on equal to 1:

filter1L =
VAR _1L_sum =
    FILTER (
        ADDCOLUMNS (
            VALUES ( 'Table'[O.Number] );
            "L1_sales"; CALCULATE (
                SUM ( 'Table'[Sale] );
                FILTER ( ALLEXCEPT ( 'Table'; 'Table'[O.Number] ); 'Table'[Size] = "1.L" )
            )
        );
        [L1_sales] >= 0
            && [L1_sales] <= 0,4
            && NOT ( ISBLANK ( [L1_sales] ) )
    )
VAR _1L =
    SUMMARIZE ( _1L_sum; [O.Number] )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[O.Number] ) IN _1L; 1; 0 )

[sturlaws]

Hi, @Anonymous,

your dataset is a bit tricky to work with. Some of the O.Numbers have multiple lines for each Size. Should the filtering on >0.0 and <= 0.4 be based on each line? Or on the sum for each size pr O.number? But then again, sum of percentages is usually not a good idea.

Not sure what you want as an outcome either, but you can create a measure something like this:

filter1L =
VAR _1L =
    CALCULATETABLE (
        SUMMARIZE ( 'Table'; 'Table'[O.Number]; 'Table'[Sale] );
        FILTER (
            ALL ( 'Table' );
            'Table'[Size] = "1.L"
                && 'Table'[Sale] >= 0
                && 'Table'[Sale] <= 0,4
        )
    )
VAR _1L_2 =
    SUMMARIZE ( _1L; 'Table'[O.Number] )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[O.Number] ) IN _1L_2; 1; 0 )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Anonymous]

Hi Strula,

Thanks for your reply.

O.Numbers have multiple lines for each Size (Different size)

filtering on >0.0 and <= 0.4 should be on Colum 2 (1.L) of the matrix (and the same should effect /drive other columns also (1.M,1.XL etc)

Mainly we are concentrating of one product variant 1.L (eliminating the blanks,outliners  and etc)

Regards,

Samueal