Forum Discussion
Filter matrix colum
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
12 Replies
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.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
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.
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?
