Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aa8057
Regular Visitor

Variance measure problem

Hi,

 

Looking for advice on how to calculate year on year variance using COUNT. Please can someone tell me how to do this?

I want to count the number of Customer ID's by year and show the  +/- difference for each year.

 

I've tried the following formula which results in the waterfall below:

CALCULATE(COUNT('Customer'[Customer ID Number]))-PREVIOUSYEAR('Year'[Entry Year V2])

 

Can someone explain to me how I get this waterfall to show the difference rather than the count for each year?

Waterfall.png

Thanks

 

4 REPLIES 4
v-qiuyu-msft
Community Support
Community Support

Hi @aa8057,

 

In your scenario, you need to create a calculated column which will return difference values between years. Then place the column field in waterfall chart Y Axis property. Please refer to the sample below:

 

q1.PNGq2.PNG

 

DatePrevious = CALCULATE(MAX(Table1[Date]), (FILTER(Table1, EARLIER(Table1[Team ID])=Table1[Team ID] && EARLIER(Table1[Date])>Table1[Date])))

 

ValuePrevious = CALCULATE(SUM(Table1[Sales]), (FILTER(Table1, EARLIER(Table1[Team ID])=Table1[Team ID] && EARLIER(Table1[DatePrevious])=Table1[Date])))

 

Change in sales = if(Table1[ValuePrevious]=0, 0, Table1[Sales]-Table1[ValuePrevious])

 

If you have any question, please feel free to ask.

 

Best Regards,
Qiuyun Yu

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Qiuyun Yu,

 

Thanks for getting back to me on this. I have tried applying the logic in your reply however I am still struggling to get this to work.

Each row in my data relates to an individual customer finace application and contains information about the customer and their application. One of the fields is the 'year of application'. This value is only a year value between 2011-2015. Each customer and therefore ID can appear multiple times each year dependent on how many finace applications they have submitted to the company. I therefore want to count how many total customer applications were made each year and then show the actual +/- change in the count of applications . I have mocked up some data below as an example of what im looking for. Ideally the chart should change when I use a slicer to select type of application or another value in the actual data.

 

RAW Data  
Customer IDYear of ApplicationType of Application
1232011CC
1232011Loan
1962014CC
1682015CC
1362013CC
8422012Loan 
6482015CC
1232011Loan
8472015Loan
9522014CC

 

What the waterfall should show:

Year CountDifference
20113 
20121-2
201310
201421
201531
Total10 

 

Apologies if there is a really simple solution to this however I am just beginning to get to grips with Power Bi. I hope this makes sense and thank you for any further help you can offer.

 

Matt

Hi @aa8057,

 

In your scenario, you can create a new table via expression:

 

Table = SUMMARIZE('RAW',RAW[Year of Application],"Count",COUNTA(RAW[Type of Application]))

 

Then create two calculated columns:

 

CountPrevious = calculate(SUM('Table'[Count]),filter('Table','Table'[Year of Application]=EARLIER('Table'[Year of Application])-1))


Difference = if('Table'[CountPrevious]=0, BLANK(), 'Table'[Count]-'Table'[CountPrevious])

 

p2.PNG

 

p1.PNG

 

For details, you can download the attached .PBIX file.

 

Best Regards,
Qiuyun Yu

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-qiuyu-msft @aa8057

 

The solution worked for me! Thanks. 

 

How would i incorporate a breakdown by month too? If I wanted to see the total count for this month versus this month last year?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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