Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
NETO13
Regular Visitor

Count a dynamic measure

‎06-29-2020 04:45 PM

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.

Labels:
Message 1 of 9
909 Views
0
8 REPLIES 8
Ashish_Mathur
Super User
Super User

‎06-29-2020 07:29 PM

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 8 of 9
851 Views
0
NETO13
Regular Visitor
In response to Ashish_Mathur

‎06-30-2020 05:43 AM

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 IDCustomer NameRegionProductQuarterQuarter AgingQuarter Week NumerOrders RevenueOrders Margin
2134590ABBUSZZ2020-Q2-41$1,000,000.00$500,000.00
1235857ACCBRZZ2020-Q3-32$950,000.00$380,000.00
9023411ADDMXXX2020-Q1-53$850,000.00$297,500.00
8923452AEEARXX2020-Q4-23$789,200.00$276,220.00
4390563AFFCLZZ2019-Q2-82$589,000.00$235,600.00
2348595EGGCOLCC2020-Q3-37$120,350.00$60,175.00
1010293FFGARVV2020-Q3-38$1,000,500.00$300,150.00
1928350BBBBRVV2019-Q2-810$700,589.00$280,235.60
2348950EDDPRCC2020-Q1-111$123,989.00$61,994.50
2134590ABBUSZZ2021-Q2010$4,398,123.00$1,099,530.75
1235857ACCBRZZ2021-Q1-113$3,895,040.00$1,129,561.60
9023411ADDMXXX2021-Q205$89,034.00$44,517.00
8923452AEEARXX2021-Q1-16$120,930.00$60,465.00
4390563AFFCLZZ2021-Q1-15$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.
 
 
Message 9 of 9
823 Views
0
Anonymous
Not applicable

‎06-29-2020 06:59 PM

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!
Message 5 of 9
864 Views
0
NETO13
Regular Visitor
In response to Anonymous

‎06-30-2020 05:39 AM

@Anonymous 

this is what the data looks like:

Customer IDCustomer NameRegionProductQuarterQuarter AgingQuarter Week NumerOrders RevenueOrders Margin
2134590ABBUSZZ2020-Q2-41$1,000,000.00$500,000.00
1235857ACCBRZZ2020-Q3-32$950,000.00$380,000.00
9023411ADDMXXX2020-Q1-53$850,000.00$297,500.00
8923452AEEARXX2020-Q4-23$789,200.00$276,220.00
4390563AFFCLZZ2019-Q2-82$589,000.00$235,600.00
2348595EGGCOLCC2020-Q3-37$120,350.00$60,175.00
1010293FFGARVV2020-Q3-38$1,000,500.00$300,150.00
1928350BBBBRVV2019-Q2-810$700,589.00$280,235.60
2348950EDDPRCC2020-Q1-111$123,989.00$61,994.50
2134590ABBUSZZ2021-Q2010$4,398,123.00$1,099,530.75
1235857ACCBRZZ2021-Q1-113$3,895,040.00$1,129,561.60
9023411ADDMXXX2021-Q205$89,034.00$44,517.00
8923452AEEARXX2021-Q1-16$120,930.00$60,465.00
4390563AFFCLZZ2021-Q1-15$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
 

 

 

 

Message 6 of 9
826 Views
0
Anonymous
Not applicable
In response to NETO13

‎06-30-2020 11:19 PM

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!
Message 7 of 9
813 Views
0
jdbuchanan71
Super User
Super User

‎06-29-2020 06:34 PM

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

Message 2 of 9
870 Views
0
NETO13
Regular Visitor
In response to jdbuchanan71

‎06-30-2020 05:10 AM

@jdbuchanan71 
Didn't worked.

 

the category is a created measure

Message 3 of 9
829 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All