Count a dynamic measure

[NETO13]

Hi,

I'm trying to calculate a dinstinct count of rows for a specific measure and couldn't find a way to do this.

The scenario:

I have a table with customers, quarters, quarter aging, revenue and margin.

I created measures for LY revenue and margin depending on the quarter selected.

 - if selected quarter Q1, it will calculate revenue for the -4 quarter aging - [this should be dynamic for any quarter selection]

With that, I created also Y/Y revenue and Y/Y Margin, and so far so good.

Now I classified the accounts based on the Y/Y values. Example: IF(AND([Y/Y Rev]>=0, [Y/Y Mgn]>=0, "I", BLANK())

This gives me four types of customers ( I, II, III and IV) depending on how they are performing.

I can select which type of customer I want to see in a table, depending on the quarter selection and works fine, but I can't count how many customers are on the selection (I, II, III or IV).

Thank you.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file with all your calculations.

[NETO13]

Hi @Ashish_Mathur ,

sorry but I can't share the PBI that I'm working for.  See if the below helps to understand better:

this is what the data looks like:

Customer ID	Customer Name	Region	Product	Quarter	Quarter Aging	Quarter Week Numer	Orders Revenue	Orders Margin
2134590	ABB	US	ZZ	2020-Q2	-4	1	$1,000,000.00	$500,000.00
1235857	ACC	BR	ZZ	2020-Q3	-3	2	$950,000.00	$380,000.00
9023411	ADD	MX	XX	2020-Q1	-5	3	$850,000.00	$297,500.00
8923452	AEE	AR	XX	2020-Q4	-2	3	$789,200.00	$276,220.00
4390563	AFF	CL	ZZ	2019-Q2	-8	2	$589,000.00	$235,600.00
2348595	EGG	COL	CC	2020-Q3	-3	7	$120,350.00	$60,175.00
1010293	FFG	AR	VV	2020-Q3	-3	8	$1,000,500.00	$300,150.00
1928350	BBB	BR	VV	2019-Q2	-8	10	$700,589.00	$280,235.60
2348950	EDD	PR	CC	2020-Q1	-1	11	$123,989.00	$61,994.50
2134590	ABB	US	ZZ	2021-Q2	0	10	$4,398,123.00	$1,099,530.75
1235857	ACC	BR	ZZ	2021-Q1	-1	13	$3,895,040.00	$1,129,561.60
9023411	ADD	MX	XX	2021-Q2	0	5	$89,034.00	$44,517.00
8923452	AEE	AR	XX	2021-Q1	-1	6	$120,930.00	$60,465.00
4390563	AFF	CL	ZZ	2021-Q1	-1	5	$590,320.00	$177,096.00

Measures created:

• Total revenue: removing any quarter selected - 
  CALCULATE(SUM(Actual[Orders Revenue]),ALL('Quarter'[Quarter]))
• LY Revenue: conditional based on the quarter selected - nested IF:

LY Rev = IF(FIRSTNONBLANK('Quarter'[Quarter Aging],1)="0",

CALCULATE([Total Rev],'Quarter'[Quarter Aging]="-4"),

IF(FIRSTNONBLANK('Quarter'[Quarter Aging],1)="-1",

CALCULATE([Total Rev],'Quarter'[Quarter Aging]="-5"),

IF(FIRSTNONBLANK('Quarter'[Quarter Aging],1)="-2",

CALCULATE([Total Rev],'Quarter'[Quarter Aging]="-6").... and goes for -13 Quarter Aging.

• Running total Revenue QTD and LY - this is to sum the revenue as the weeks of the quarter goes by to give cummulative revenue

RTotal Rev =

CALCULATE (

SUM ( Actual[Orders Revenue] ),

FILTER ( ALLSELECTED('Quarter'),

'Quarter'[Quarter Week Num] <= MAX ( 'Quarter'[Quarter Week Num] )

))

• Y/Y Rev and margin comparison

Y/Y Rev =

(ROUNDUP([RTotal Rev],3) / [RTotal LY Rev]) -1

• And measures for the Account classification

Quadrant I = IF(AND([Y/Y Rev]>=0,[Y/Y GM]>=0),"I",BLANK())

Quadrant II = IF(AND([Y/Y Rev]<0,[Y/Y GM]>=0),"II",BLANK())

Quadrant III = IF(AND([Y/Y Rev]<0,[Y/Y GM]<=0),"III",BLANK())

Quadrant IV = IF(AND([Y/Y Rev]>0,[Y/Y GM]<=0),"IV",BLANK())

Situation

If I select a TABLE View and put Account name and quadrant, I can have the list of the accounts that are being classified within that, but I don't have a way to count how many accounts are those.

For each time that I select a quarter, all the measures calculate the LY and Y/Y and give a different classification, as you can see in the formulas.

Outcome expected: be able to quantify how many accounts are increasing and/or decreasing Y/Y depending on the quarter selected.

[Anonymous]

Hi @NETO13 ,

Whether the type is a calculated column or measure?Can you provide some sample data for testing?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[NETO13]

@Anonymous 

this is what the data looks like:

Customer ID	Customer Name	Region	Product	Quarter	Quarter Aging	Quarter Week Numer	Orders Revenue	Orders Margin
2134590	ABB	US	ZZ	2020-Q2	-4	1	$1,000,000.00	$500,000.00
1235857	ACC	BR	ZZ	2020-Q3	-3	2	$950,000.00	$380,000.00
9023411	ADD	MX	XX	2020-Q1	-5	3	$850,000.00	$297,500.00
8923452	AEE	AR	XX	2020-Q4	-2	3	$789,200.00	$276,220.00
4390563	AFF	CL	ZZ	2019-Q2	-8	2	$589,000.00	$235,600.00
2348595	EGG	COL	CC	2020-Q3	-3	7	$120,350.00	$60,175.00
1010293	FFG	AR	VV	2020-Q3	-3	8	$1,000,500.00	$300,150.00
1928350	BBB	BR	VV	2019-Q2	-8	10	$700,589.00	$280,235.60
2348950	EDD	PR	CC	2020-Q1	-1	11	$123,989.00	$61,994.50
2134590	ABB	US	ZZ	2021-Q2	0	10	$4,398,123.00	$1,099,530.75
1235857	ACC	BR	ZZ	2021-Q1	-1	13	$3,895,040.00	$1,129,561.60
9023411	ADD	MX	XX	2021-Q2	0	5	$89,034.00	$44,517.00
8923452	AEE	AR	XX	2021-Q1	-1	6	$120,930.00	$60,465.00
4390563	AFF	CL	ZZ	2021-Q1	-1	5	$590,320.00	$177,096.00

Measures created:

• Total revenue: removing any quarter selected - 
  CALCULATE(SUM(Actual[Orders Revenue]),ALL('Quarter'[Quarter]))
• LY Revenue: conditional based on the quarter selected - nested IF:

LY Rev = IF(FIRSTNONBLANK('Quarter'[Quarter Aging],1)="0",

CALCULATE([Total Rev],'Quarter'[Quarter Aging]="-4"),

IF(FIRSTNONBLANK('Quarter'[Quarter Aging],1)="-1",

CALCULATE([Total Rev],'Quarter'[Quarter Aging]="-5"),

IF(FIRSTNONBLANK('Quarter'[Quarter Aging],1)="-2",

CALCULATE([Total Rev],'Quarter'[Quarter Aging]="-6").... and goes for -13 Quarter Aging.

• Running total Revenue QTD and LY - this is to sum the revenue as the weeks of the quarter goes by to give cummulative revenue

RTotal Rev =

CALCULATE (

SUM ( Actual[Orders Revenue] ),

FILTER ( ALLSELECTED('Quarter'),

'Quarter'[Quarter Week Num] <= MAX ( 'Quarter'[Quarter Week Num] )

))

• Y/Y Rev and margin comparison

Y/Y Rev =

(ROUNDUP([RTotal Rev],3) / [RTotal LY Rev]) -1

• And measures for the Account classification

Quadrant I = IF(AND([Y/Y Rev]>=0,[Y/Y GM]>=0),"I",BLANK())

Quadrant II = IF(AND([Y/Y Rev]<0,[Y/Y GM]>=0),"II",BLANK())

Quadrant III = IF(AND([Y/Y Rev]<0,[Y/Y GM]<=0),"III",BLANK())

Quadrant IV = IF(AND([Y/Y Rev]>0,[Y/Y GM]<=0),"IV",BLANK())

Situation

If I select a TABLE View and put Account name and quadrant, I can have the list of the accounts that are being classified within that, but I don't have a way to count how many accounts are those.

For each time that I select a quarter, all the measures calculate the LY and Y/Y and give a different classification, as you can see in the formulas.

Outcome expected: be able to quantify how many accounts are increasing and/or decreasing Y/Y depending on the quarter selected.

See if this is helpful.

Thank you

[Anonymous]

Hi @NETO13 ,

So what's in measure [RTotal LY Rev] and [Y/Y GM]?

Based on my understanding,you'd better create a new table using below dax expression:

Table 2 = DISTINCT('Table'[Quarter])

It will be used in slicer.

Then modify your measures for  Total revenue,LY Rev and RTotal Rev as below:

Total revenue = CALCULATE(SUM('Quarter'[Orders Revenue]),FILTER(ALL('Quarter'),'Quarter'[Quarter]<>MAX('Table 2'[Quarter])))

RTotal Rev = 
CALCULATE (
SUM ( 'Quarter'[Orders Revenue]),
FILTER (ALL('Quarter'),'Quarter'[Quarter]=SELECTEDVALUE('Table 2'[Quarter])&&'Quarter'[Quarter Week Numer]<=MAX('Quarter'[Quarter Week Numer])))

LY Rev = IF(FIRSTNONBLANK('Quarter'[Quarter Aging],1)="0",
CALCULATE('Quarter'[Total revenue],'Quarter'[Quarter Aging]="-4"),
IF(FIRSTNONBLANK('Quarter'[Quarter Aging],1)="-1",
CALCULATE('Quarter'[Total revenue],'Quarter'[Quarter Aging]="-5"),
IF(FIRSTNONBLANK('Quarter'[Quarter Aging],1)="-2",
CALCULATE('Quarter'[Total revenue],'Quarter'[Quarter Aging]="-6"),BLANK())))

Try if the above measures would work.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@NETO13 , do you want slicer for that? Then you need to try binning

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[jdbuchanan71]

@NETO13 

How a you selecting the customers you want to see in a visual?  If you want to count just the customer with a category of "IV" something like this would work.

IV Count = COUNTROWS( FILTER ( VALUES(Sales[Customer]),[Customer Group] = "IV"))

But I'm not sure that is what you are looking to achieve. 

[NETO13]

@jdbuchanan71 
Didn't worked.

the category is a created measure