Forum Discussion
Solved
MULTIPLE FILTRATION
I have the data like this...
| YEAR | SHORTNAME | DEALER | TYPE | PRODUCT | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC | JAN | FEB | MAR | TOT | CATEGORY |
| 20182019 | AB | A | STD | PROD | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 10 | PRODUCT |
| 20182019 | AC | B | STD | PROD | 20 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 20 | PRODUCT |
| 20182019 | AD | A | STD | PROD | 30 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 30 | PRODUCT |
| 20182019 | AB | A | STD | CAB | 0 | 40 | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 50 | CAB |
| 20182019 | AB | A | STD | CAB | 0 | 50 | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 60 | CAB |
| 20182019 | AC | A | STD | CAB | 0 | 0 | 0 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 450 | CAB |
| 20182019 | AD | A | STD | CAB | 0 | 0 | 0 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 450 | CAB |
| 20192020 | AB | A | STD | PROD | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 10 | PRODUCT |
| 20192020 | AC | B | STD | PROD | 20 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 20 | PRODUCT |
| 20192020 | AD | A | STD | PROD | 30 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 30 | PRODUCT |
| 20192020 | AB | A | STD | CAB | 0 | 40 | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 50 | CAB |
| 20192020 | AB | A | STD | CAB | 0 | 50 | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 60 | CAB |
| 20192020 | AC | A | STD | CAB | 0 | 0 | 0 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 450 | CAB |
| 20192020 | AD | A | STD | CAB | 0 | 0 | 0 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 450 | CAB |
I Want a result like this
| I want COUNT OF DEALERS SAY | ||||
| MONTH | DEALERS | QTY<10 | QTY>10<=20 | QTY>=30 |
| APRIL | 6 | 2 | 2 | 2 |
| MAY | 4 | 0 | 0 | 4 |
Please guide me..
v-chuncz-msft wrote:You may use the following measure.
Measure = COUNTROWS ( FILTER ( VALUES ( cab[DEALER] ), CALCULATE ( SUM ( cab[QTY] ) <= 1000 ) ) )it gives only true or false...
I applied this measure ... and works fine..
qty500 = CALCULATE (DISTINCTCOUNT('BREAKUP CABLES'[DEALER] ),FILTER ( ALL ( 'BREAKUP CABLES'[DEALER] ), 'BREAKUP CABLES'[sumqty]<=499))
7 Replies
Hi srkase ,
thanks for explaining clearly.
Here's the output from your provided data.
Here are the steps. I just unpivot the months and filtered the qty <> 0
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtABiSyUdJUcnEAHEwSEuQDIgyB9EGRoACVIwWANIc6hziFKsDqoVzkDCCd0KI1KtMMJnhQs2XxiTaoUxPitQA8oZzAepN4H7nxhsagDVTKzxpiQZb4bLeGccxqO4i1jCBKcvXGhqjaWRATgV0DDRwq2gXaKFW0G7RIsjoKiVaAkaT1miRYkDOqQmWiXaWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [YEAR = _t, SHORTNAME = _t, DEALER = _t, TYPE = _t, PRODUCT = _t, APR = _t, MAY = _t, JUN = _t, JUL = _t, AUG = _t, SEP = _t, OCT = _t, NOV = _t, DEC = _t, JAN = _t, FEB = _t, MAR = _t, TOT = _t, CATEGORY = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"YEAR", Int64.Type}, {"SHORTNAME", type text}, {"DEALER", type text}, {"TYPE", type text}, {"PRODUCT", type text}, {"APR", Int64.Type}, {"MAY", Int64.Type}, {"JUN", Int64.Type}, {"JUL", Int64.Type}, {"AUG", Int64.Type}, {"SEP", Int64.Type}, {"OCT", Int64.Type}, {"NOV", Int64.Type}, {"DEC", Int64.Type}, {"JAN", Int64.Type}, {"FEB", Int64.Type}, {"MAR", Int64.Type}, {"TOT", Int64.Type}, {"CATEGORY", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"YEAR", "SHORTNAME", "DEALER", "TYPE", "PRODUCT", "TOT", "CATEGORY"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Month"}, {"Value", "Qty"}}), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Qty] <> 0)) in #"Filtered Rows"measures for the qty:
Qty<10 = CALCULATE(COUNT('Table'[DEALER]), 'Table'[Qty] <=10)Qty<20 = CALCULATE(COUNT('Table'[DEALER]), 'Table'[Qty] >10 && 'Table'[Qty] <=20)Qty>30 = CALCULATE(COUNT('Table'[DEALER]), 'Table'[Qty] >=30)Dear Mussaedna ,
I have used query editor to unpivot the data and applied the measure what you have given,,,
but couldnt get the result
If you will try it on the data you gave, it will work.
if it didn't work, you should at least explain why or what problem you have encountered so it can be resolved.
Thank you.
