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
asoegiantoro
Frequent Visitor

Creating percentage from a monthly report

Hi PowerBI Expert,

A beginner here, I wanted to ask if I can create a percentage measure for approval with different measure. The measure that I use include gender, month and location.

 

Year-Month = FORMAT( IF(LEN(summary_los[applied_month]) = 1, summary_los[applied_year] & "-0" & summary_los[applied_month], summary_los[applied_year] & "-" & summary_los[applied_month]), "YYYY-MM")

 

total_register = CALCULATE(
COUNTA('summary_los'[decision]),
ALLSELECTED('summary_los'[decision]))

 

count_approved =
CALCULATE(COUNTROWS(FILTER(summary_los, 'summary_los'[decision] = "Approved")))

 

Percentage= DIVIDE( [total_register] , summary_los[count_approved])

 

For the percentage, it did not work since there's a circular dependency detected. I am wondering how can I get percentage of approval. I want to see the percentage in monthly basis of approval 

 

My data look like: 

 

application_no gender cif applied_date applied_year applied_month branch_name location decision
10001 M 131522 20180113 2018 1 No Branch Name No Branch Location Approved
10004 M 132551 20180113 2018 1 No Branch Name No Branch Location Approved
10010 M 134887 20180113 2018 1 No Branch Name No Branch Location Declined
10012 M 134872 20180113 2018 1 No Branch Name No Branch Location Canceled
10014 M 132316 20180113 2018 1 No Branch Name No Branch Location Approved
10018 M 126585 20180113 2018 1 No Branch Name No Branch Location Approved
10019 M 124892 20180113 2018 1 No Branch Name No Branch Location Approved
10026 M 133385 20180113 2018 1 No Branch Name No Branch Location Approved
10028 F 127549 20180113 2018 1 No Branch Name No Branch Location Approved
10029 M 128895 20180113 2018 1 No Branch Name No Branch Location Declined
1003 M 124560 20171001 2017 10 No Branch Name No Branch Location Canceled
10030 M 134842 20180113 2018 1 No Branch Name No Branch Location Approved
10033 M 133402 20180113 2018 1 No Branch Name HCI Online 1 Approved
10034 F 134900 20180113 2018 1 No Branch Name HCI Online 1 Approved

 

And below is what my current dashboard looks like:

Capture.PNG

 

 

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

 

The Year-Month field should come from the Calendar Table.  There should be a relationship from the Date column of your Summary_los table to the Date column of your Calendar Table.  Also, your measure can be simplified to:

 

total_register = COUNTA('summary_los'[decision])

count_approved =CALCULATE([total_register],'summary_los'[decision]="Approved")


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

1 REPLY 1
Ashish_Mathur
Super User
Super User

Hi,

 

The Year-Month field should come from the Calendar Table.  There should be a relationship from the Date column of your Summary_los table to the Date column of your Calendar Table.  Also, your measure can be simplified to:

 

total_register = COUNTA('summary_los'[decision])

count_approved =CALCULATE([total_register],'summary_los'[decision]="Approved")


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.