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
jkhan
Helper III
Helper III

Division in Matrix

Hi, 

 

Please help me to build DAX to meet below requirement, I have created 2 MATRIX Visual Revenue and Expense

Account_ID starting with 4 is Revenue Account
Account_ID starting with 5 is Expense Account

So I have made Visual Level Filter for

Revenue Account_Type='Revenue' & Expense Account_Type='Expense'

 

I written below DAX for JAN-2022 

 

A_JAN = CALCULATE (SUM (PBI_MIS_TB[ACTUAL]), PBI_MIS_TB[A_MONTH]=1)

Now I have requirement to get Percentage of  Expense where Different Account Expenses should be divided with Total Revenue 166,801021 
 
Example :  
62,794,480 / 166,801,021
46,539,963 / 166,801,021
 
Below DAX for Revenue is working fine. 
A_JAN% = DIVIDE( [A_JAN], CALCULATE( [A_JAN] ,ALLSELECTED()) )

But below DAX for Expense is not working correctly.
E_A_JAN1% = DIVIDE( CALCULATE ( SUM (PBI_MIS_TB[ACTUAL]), PBI_MIS_TB[A_MONTH]=1,PBI_MIS_TB[TB_ACCOUNT_TYPE]="REVENUE" ), CALCULATE( [A_JAN] ,ALLSELECTED()) )


2.JPG 

 

Thanks & Regards

Jamsher

1 ACCEPTED SOLUTION
jkhan
Helper III
Helper III

Able to build the DAX working fine now. 

 

E_A_JAN% = DIVIDE( [A_JAN],CALCULATE ( SUM (PBI_MIS_TB[ACTUAL]), PBI_MIS_TB[A_MONTH]=1,PBI_MIS_TB[TB_ACCOUNT_TYPE]="REVENUE", REMOVEFILTERS(PBI_MIS_TB)) )
 
3.JPG

 

Thanks & Regards
Jamsher

View solution in original post

2 REPLIES 2
jkhan
Helper III
Helper III

Able to build the DAX working fine now. 

 

E_A_JAN% = DIVIDE( [A_JAN],CALCULATE ( SUM (PBI_MIS_TB[ACTUAL]), PBI_MIS_TB[A_MONTH]=1,PBI_MIS_TB[TB_ACCOUNT_TYPE]="REVENUE", REMOVEFILTERS(PBI_MIS_TB)) )
 
3.JPG

 

Thanks & Regards
Jamsher
jkhan
Helper III
Helper III

@amitchandak  @Whitewater100 

Dear Mr. Amit , Mr. Whitewater any suggestion please

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.

Top Solution Authors