MULTIPLE FILTRATION

[srkase]

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..

[mussaenda]

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)

[srkase]

Dear Mussaedna ,

I have used query editor to unpivot the data and applied the measure what you have given,,,

but couldnt get the result

[mussaenda]

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.

[srkase]

THIS WAS THE SAMPLE DATA

YEAR	SHORTNAME	DEALER	TYPE	PRODUCT	CATEGORY	MONTH	QTY
20192020	CBE	A	STD	A	CABLE	APR	1000
20192020	CBE	A	STD	B	CABLE	APR	3000
20192020	CBE	A	STD	A	CABLE	APR	1738
20192020	CBE	A	STD	B	CABLE	APR	3000
20192020	CBE	A	STD	A	CABLE	APR	3940
20192020	CBE	B	STD	B	CABLE	APR	1000
20192020	CBE	C	STD	A	CABLE	APR	1000
20192020	CBE	C	STD	B	CABLE	APR	1800
20192020	CBE	C	STD	A	CABLE	APR	3000
20192020	CBE	D	STD	B	CABLE	APR	1000
20192020	CBE	D	STD	A	CABLE	APR	1000
20192020	CBE	D	STD	B	CABLE	APR	2869
20192020	CBE	D	STD	A	CABLE	APR	3000
20192020	CBE	E	STD	B	CABLE	APR	1000
20192020	CBE	E	STD	A	CABLE	APR	872
20192020	CBE	E	STD	B	CABLE	APR	4000
20192020	CBE	F	STD	A	CABLE	APR	1000
20192020	CBE	F	STD	B	CABLE	APR	2000
20192020	CBE	G	STD	A	CABLE	APR	1000
20192020	CBE	G	STD	B	CABLE	APR	1000
20192020	CBE	G	STD	A	CABLE	APR	2000
20192020	CBE	G	STD	B	CABLE	APR	6000
20192020	CBE	H	STD	A	CABLE	APR	2000
20192020	CBE	I	STD	B	CABLE	APR	2000
20192020	CBE	I	STD	A	CABLE	APR	3877
20192020	CBE	I	STD	B	CABLE	APR	4000
20192020	CBE	J	STD	A	CABLE	APR	1000
20192020	CBE	J	STD	B	CABLE	APR	2000
20192020	CBE	J	STD	A	CABLE	APR	6000
20192020	CBE	K	STD	B	CABLE	APR	1000
20192020	CBE	K	STD	A	CABLE	APR	4000
20192020	CBE	L	STD	B	CABLE	APR	1000
20192020	CBE	L	STD	A	CABLE	APR	890
20192020	CBE	M	STD	B	CABLE	APR	2000
20192020	CBE	M	STD	A	CABLE	APR	6000
20192020	CBE	M	STD	B	CABLE	APR	12000
20182019	CBE	M	STD	A	CABLE	APR	6000
20182019	CBE	M	STD	B	CABLE	APR	12000
20182019	CBE	M	STD	A	CABLE	APR	1000
20182019	CBE	M	STD	B	CABLE	APR	3000
20182019	CBE	M	STD	A	CABLE	APR	1738
20182019	CBE	M	STD	B	CABLE	APR	3000
20182019	CBE	M	STD	A	CABLE	APR	3940
20182019	CBE	L	STD	B	CABLE	APR	1000
20182019	CBE	K	STD	A	CABLE	APR	1000
20182019	CBE	K	STD	B	CABLE	APR	1800
20182019	CBE	K	STD	A	CABLE	APR	3000
20182019	CBE	I	STD	B	CABLE	APR	1000
20182019	CBE	I	STD	A	CABLE	APR	1000
20182019	CBE	I	STD	B	CABLE	APR	2869
20182019	CBE	I	STD	A	CABLE	APR	3000
20182019	CBE	J	STD	B	CABLE	APR	1000
20182019	CBE	J	STD	A	CABLE	APR	872
20182019	CBE	J	STD	B	CABLE	APR	4000
20182019	CBE	A	STD	A	CABLE	APR	2000
20182019	CBE	A	STD	B	CABLE	APR	6000
20182019	CBE	A	STD	A	CABLE	APR	12000
20182019	CBE	A	STD	B	CABLE	APR	6000
20182019	CBE	A	STD	A	CABLE	APR	12000

The measure i have used to filter <1000,

Qty<1000 = CALCULATE(COUNT('cab'[DEALER]),cab[QTY] <=1000)

the result i get is

Month  Count of Dealer   Qty<1000

April              13                       21

I have kept distinct count of dealer

Results are wrong

[srkase]

In a month a dealer might have taken items in many invoices... The sum we should check and get the total and then the measures should be applied..

[v-chuncz-msft]

@srkase 

You may use the following measure.

Measure =
COUNTROWS (
    FILTER ( VALUES ( cab[DEALER] ), CALCULATE ( SUM ( cab[QTY] ) <= 1000 ) )
)

[srkase · Accepted solution]

---

@v-chuncz-msft wrote:

@srkase 

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))