Reply
Member
Posts: 256
Registered: ‎09-08-2016
Accepted Solution

Anyone Know how to Calculate a Perecentage?

Hello,

 

 I have one of the most simple calculations in the entire word. What is the percentage of revenue.  It is a simple question of which Power BI so far can not answer.

 

The default Percent of Grand Total can not be used becouse it is giving a percentage of the entire total where all I want to do is find the percentage of revenue. NOT an easy task for Power BI.

 

Very Simply, In my trial balance report, I want to add a column that simply gives me on each line what the percentage of revenue is.

 

Nothing, has worked so far. I am including the PBIX file in case anyone is interested in seeing how difficult it is  or maybee even impossible it is to simply have a calculation that gives each line item a percentage of revenue.  

 

I could be wrong, but a simple formula will not work.   I am thinking it is becouse I use a Measure for the amount, it prevents any kind of formula from working as I need.  

 

Thanks

 

 

 Trial Balance

 


Accepted Solutions
Established Member
Posts: 194
Registered: ‎04-13-2016

Re: Anyone Know how to Calculate a Percentage?

[ Edited ]

Hi @lcasey

 

I agree this is not a very easy one. The solution highly depends on what you really want to achieve.

I have a suggestion - maybe not the best one ... well let's see !

 

I am using variables (Excel 2016, Power BI Desktop) to optimise the code and make it easier to read.

 

This measure should work:

InPctOfRevenue = VAR Amt = [Amount]
VAR TotalRevenue = IF(Amt , CALCULATE([Amount] , FILTER(ALL('00-COASlicer') ,'00-COASlicer'[PLTYPE]="Revenue")) , BLANK()) RETURN
ABS(DIVIDE(Amt TotalRevenue ))

I had to use an IF statement to display only the items with a positive amount.

 

BUT

I notice you have some slicers coming from 00-COASlicer table ("Organization Selection" and "Account Selection"). Do want them to impact the percentage calculation above ? If so, then you would need something like

InPctOfRevenue2 = VAR Amt = [Amount]
VAR TotalRevenue = IF(Amt , CALCULATE([Amount] , FILTER(ALLSelected('00-COASlicer') ,'00-COASlicer'[PLTYPE]="Revenue")) , BLANK()) RETURN
ABS(DIVIDE(Amt TotalRevenue ))

 

Otherwise, if you user choices through "Organization Selection" and "Account Selection" slicers are not to modify the "Total revenue" calculation, my first measure (InPctOfRevenue) should always be correct.

It is much easier to cope with this requirement with normalized data models because you can leverage DAX Filter restoration. But you may not be able to control your data model.

 

Please let us know if it works anyway !

View solution in original post


All Replies
Established Member
Posts: 194
Registered: ‎04-13-2016

Re: Anyone Know how to Calculate a Percentage?

[ Edited ]

Hi @lcasey

 

I agree this is not a very easy one. The solution highly depends on what you really want to achieve.

I have a suggestion - maybe not the best one ... well let's see !

 

I am using variables (Excel 2016, Power BI Desktop) to optimise the code and make it easier to read.

 

This measure should work:

InPctOfRevenue = VAR Amt = [Amount]
VAR TotalRevenue = IF(Amt , CALCULATE([Amount] , FILTER(ALL('00-COASlicer') ,'00-COASlicer'[PLTYPE]="Revenue")) , BLANK()) RETURN
ABS(DIVIDE(Amt TotalRevenue ))

I had to use an IF statement to display only the items with a positive amount.

 

BUT

I notice you have some slicers coming from 00-COASlicer table ("Organization Selection" and "Account Selection"). Do want them to impact the percentage calculation above ? If so, then you would need something like

InPctOfRevenue2 = VAR Amt = [Amount]
VAR TotalRevenue = IF(Amt , CALCULATE([Amount] , FILTER(ALLSelected('00-COASlicer') ,'00-COASlicer'[PLTYPE]="Revenue")) , BLANK()) RETURN
ABS(DIVIDE(Amt TotalRevenue ))

 

Otherwise, if you user choices through "Organization Selection" and "Account Selection" slicers are not to modify the "Total revenue" calculation, my first measure (InPctOfRevenue) should always be correct.

It is much easier to cope with this requirement with normalized data models because you can leverage DAX Filter restoration. But you may not be able to control your data model.

 

Please let us know if it works anyway !

Member
Posts: 256
Registered: ‎09-08-2016

Re: Anyone Know how to Calculate a Perecentage?

Hello and Thanks,

 

How would I normalize this data?   It is Normalized as far as I can tell.

 

My Fact Table is the GL table and everything else is a slicer. 

 

The COA Table is the 1 chart of accounts table that has no duplicates,  The COASlicer slices the data by multiple columns.

 

I als have a permission table striictly for permissions as this company has granular account level permissions.  and the Calendar table is used for date slicing.

 

I dont see anything that isnt normalized, could you please help explain what I would need to do in order to normalize the data making DAX calcualtions easier?

Established Member
Posts: 194
Registered: ‎04-13-2016

Re: Anyone Know how to Calculate a Perecentage?

@lcasey

 

Normalizing would mean here breaking into several pieces your 00-COASlicer table.

1 Table for the Org1, Org2, etc.

1 Table for PL Type

1 Table for PL Category

with foreign keys linking to the 00-COASlicer table.

 

But that may not be easier actually.

 

Try my first measure and let me know !

Member
Posts: 256
Registered: ‎09-08-2016

Re: Anyone Know how to Calculate a Perecentage?

[ Edited ]

Yes, Your measures worked perfect and I will need to really investigate the formula to understand what you did.  (I am very new to DAX)

 

I agree with you on normalization of the COA Slicer, I thought of that also, but discovered the same thing, it may not make things easier.

 

The COASlicer can be broken out and I have been struggling with how much of it to normalize.  Although the total slicer is less than 50 columns so I have been waiting until I reach the 50 column mark to determine how to seperate out data.

 

It will require more relationships though If/When I do need to normalize the COA.

 

Thank you very , very much. 

 

 

 

Established Member
Posts: 194
Registered: ‎04-13-2016

Re: Anyone Know how to Calculate a Perecentage?

That's OK ! Happy to see it helped you Smiley Happy

 

 

Highlighted
Member
Posts: 256
Registered: ‎09-08-2016

Re: Anyone Know how to Calculate a Perecentage?

Hello,

 

Could I ask you a question?  Why doesnt the formula work with Expenses?

 

I Changed the  = "Revenue"  to <> "Revenue"  and it still has the same result.

 

PCT Of Rev. = VAR Amt = [Amount]
VAR TotalRevenue = IF(Amt , CALCULATE([Amount] , FILTER(ALL('00-COASlicer') ,'00-COASlicer'[PLTYPE] <> "Revenue")) , BLANK()) RETURN
ABS(DIVIDE(Amt , TotalRevenue ))