Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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..
Solved! Go to Solution.
@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..
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.
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,
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..
You may use the following measure.
Measure = COUNTROWS ( FILTER ( VALUES ( cab[DEALER] ), CALCULATE ( SUM ( cab[QTY] ) <= 1000 ) ) )
@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..
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |