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
Satya_04
Helper I
Helper I

Percentage of total on QoQ basis in power bi

I need to calculate percentage of total on QoQ basis in power bi

 

I have created measure 1 which is 

% OF Total_Current month_CF =
var denominator =
CALCULATE(SUM('fred_h8_banking_03132023'[Amt in Bn_CF]),ALLEXCEPT('fred_h8_banking_03132023',fred_h8_banking_03132023[A/L],'fred_h8_banking_03132023'[Month],'fred_h8_banking_03132023'[banks]))

var numerator =
CALCULATE(SUM('fred_h8_banking_03132023'[Amt in Bn_CF]),ALLEXCEPT('fred_h8_banking_03132023',fred_h8_banking_03132023[A/L],'fred_h8_banking_03132023'[Month],fred_h8_banking_03132023[series_title],'fred_h8_banking_03132023'[banks]))

return DIVIDE(numerator, denominator,0)
This will give me result for say Jan 2020.
2nd measure 
% OF Total_Previous Quarter_CF =
var denominator =
CALCULATE(SUM('fred_h8_banking_03132023'[Amt in Bn_CF]),DATEADD(fred_h8_banking_03132023[Month],-1,QUARTER),ALLEXCEPT('fred_h8_banking_03132023',fred_h8_banking_03132023[A/L],'fred_h8_banking_03132023'[Month],'fred_h8_banking_03132023'[banks]))

var numerator =
CALCULATE(SUM('fred_h8_banking_03132023'[Amt in Bn_CF]),DATEADD(fred_h8_banking_03132023[Month],-1,QUARTER),ALLEXCEPT('fred_h8_banking_03132023',fred_h8_banking_03132023[A/L],'fred_h8_banking_03132023'[Month],fred_h8_banking_03132023[series_title],'fred_h8_banking_03132023'[banks]))
 
This will give me result for say Oct 2019.
 
These measures are finally used in this way to calculate QoQ
MoM % change_CF =

VAR a = [% OF Total_Current month_CF]

VAR b = [% OF Total_Previous Quarter_CF]

return  DIVIDE( a-b,a,0)
 
The answer I am getting is not correct. Please guide.
2 REPLIES 2
amitchandak
Super User
Super User

@Satya_04 , for time intelligence always use date table, joined with your date table

 

and you can avoid allexpect

 

CALCULATE(SUM('fred_h8_banking_03132023'[Amt in Bn_CF]),DATEADD(Date[Date],-1,QUARTER))

 

The date of date table  is joined with date of your table

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

 

 

@amitchandak 

Hi Amit, thank you for responding. I'll create a table for calendar separately. In the meantime, please tell me what is the error in the DAX that I have shared?

I do get some difference in the result. I think I need to keep ALLEXCEPT as I will have to use couple of filters. Please let me know your thoughts.

 

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.