How to Count Conditional Visual Column based on DAX

[Anonymous]

Hi All,

I have cummulative perecnetages driven by DAX in term of context transition. On that percentages i have applied condition by AA,A,C & D which is working correctly , now i want count how many AA , A, B C & D , Could you please assist. 

All measures calculated based on DAX

% of Cummulative Total = DIVIDE([Cummulative total],[sum of total orders by SKU])

Class by cumm % = if(

NOT(ISBLANK([% of Cummulative Total])),

switch(

TRUE(),

[% of Cummulative Total] <= 0.40, "AA",

[% of Cummulative Total] <= 0.60, "A" ,

[% of Cummulative Total] <= 0.75,"B",

[% of Cummulative Total] <= 0.85,"C",

[% of Cummulative Total] <= 1,"D",

"None"

))

[v-rongtiep-msft]

Hi @Anonymous ,

Please have a try.

Create a measure.

coutncolumn = CALCULATE(COUNT('Table'[Class by cumm]),FILTER(ALL('Table'),'Table'[Class by cumm]=SELECTEDVALUE('Table'[Class by cumm])))

If I have misunderstood your meaning, please provide more details with your desired output.

Best Regards

Community Support Team _ Polly

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

[Anonymous]

Hi Sir, Much appreciate your response for this query, Ok i will explian you , In raw data I have SKU & Order Columns , rest all achived by using DAX Cummulative Total , Cummulative Percentage , Class by Cumm all there is no such coulumns in raw data table.

I have below info to calculate ther metrics & measures , which i have almost done except to count how many A, B & C.

Pls let me know if you need more info

[v-rongtiep-msft]

Hi @Anonymous ,

I'm sorry I got a little confused. What is your desired output? Could you please provide you pbix file without privacy information(or some sample data) and desired output with more details( Best output in image form with text explanation)

Best Regards

Community Support Team _ Polly

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

[Anonymous]

Hi Polly,

I have done this on excel by using pivot table and some formulas. Now i am transferring this template on Power Bi.

Following is Excel Results.

Following is the sample data.

4.Cummulative total = if(

    NOT(ISBLANK([SUM Of Total OrdersAll])),

    CALCULATE(

        [SUM Of Total OrdersAll],

        FILTER(

            ALL(examp[SKU]),

            SUM(examp[Total Orders])<=[SUM Of Total OrdersAll]

        )

    )

)

1.SUM Of Total OrdersAll = sum(examp[Total Orders])

2.sum of total orders by SKU = CALCULATE([SUM Of Total OrdersAll],REMOVEFILTERS(examp[SKU]))

3.% of Orders by Month = DIVIDE([SUM Of Total OrdersAll],[sum of total orders by SKU])

5.% of Cummulative Total = DIVIDE([Cummulative total],[sum of total orders by SKU])

Class by cumm % = if(

NOT(ISBLANK([% of Cummulative Total])),

switch(

TRUE(),

[% of Cummulative Total] <= 0.40, "AA",

[% of Cummulative Total] <= 0.60, "A" ,

[% of Cummulative Total] <= 0.75,"B",

[% of Cummulative Total] <= 0.85,"C",

[% of Cummulative Total] <= 1,"D",

"None"

))

SKUMonth-YYYYTotal Orders

15163160	1/01/2021	545
15163160	2/01/2021	488
16021240	12/01/2021	332
16022320	13/01/2021	278
16298490	7/01/2021	191
26439080	6/01/2021	263
26439080	9/01/2021	168
28630060	11/01/2021	194
62953570	14/01/2021	317
62958900	3/01/2021	474
64145520	10/01/2021	199
69591300	4/01/2021	250
69591300	5/01/2021	336
69591300	8/01/2021	387
13241130	8/02/2021	303
15120560	9/02/2021	85
15131420	2/02/2021	336
15131420	1/02/2021	442
16021240	12/02/2021	287
16022320	13/02/2021	266
16298490	7/02/2021	173
26431470	5/02/2021	166
26439080	6/02/2021	240
28630060	11/02/2021	199
62953570	14/02/2021	221
62958900	3/02/2021	273
64145520	10/02/2021	212
69591300	4/02/2021	248
15163160	1/03/2021	488
15131420	2/03/2021	310
62958900	3/03/2021	289
69591300	4/03/2021	288
26431470	5/03/2021	141
26439080	6/03/2021	217
16298490	7/03/2021	371
13241130	8/03/2021	270
15120560	9/03/2021	442
64145520	10/03/2021	235
28630060	11/03/2021	204
16021240	12/03/2021	263
16022320	13/03/2021	245
62953570	14/03/2021	232
15163160	1/04/2021	7
15131420	2/04/2021	111
62958900	3/04/2021	218
69591300	4/04/2021	247
26431470	5/04/2021	238
26439080	6/04/2021	293
16298490	7/04/2021	260
13241130	8/04/2021	169
15120560	9/04/2021	70
64145520	10/04/2021	219
28630060	11/04/2021	189
16021240	12/04/2021	217
16022320	13/04/2021	236
62953570	14/04/2021	249
15163160	15/04/2021	6
15131420	16/04/2021	199
62958900	17/04/2021	213
69591300	18/04/2021	283
26431470	19/04/2021	333
26439080	20/04/2021	255
16298490	21/04/2021	199
13241130	22/04/2021	250
15120560	23/04/2021	440
64145520	24/04/2021	229
28630060	25/04/2021	236
16021240	26/04/2021	274
16022320	27/04/2021	195
62953570	28/04/2021	194
15163160	1/06/2021	912
15131420	2/06/2021	157
62958900	3/06/2021	204
69591300	4/06/2021	330
26431470	5/06/2021	366
26439080	6/06/2021	245
16298490	7/06/2021	259
13241130	8/06/2021	1
15120560	9/06/2021	155
64145520	10/06/2021	235
28630060	11/06/2021	249
16021240	12/06/2021	140
16022320	13/06/2021	6
62953570	14/06/2021	72
15163160	1/07/2021	234
15131420	2/07/2021	480
62958900	3/07/2021	336
69591300	4/07/2021	309
26431470	5/07/2021	345
26439080	6/07/2021	255
16298490	7/07/2021	246
13241130	8/07/2021	277
15120560	9/07/2021	212
64145520	10/07/2021	230
28630060	11/07/2021	285
16021240	12/07/2021	42
16022320	13/07/2021	314
62953570	14/07/2021	237