cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lcasey Member
Member

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Datatouille Established Member
Established Member

Re: Anyone Know how to Calculate a Percentage?

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 !

6 REPLIES 6
Datatouille Established Member
Established Member

Re: Anyone Know how to Calculate a Percentage?

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 !

lcasey Member
Member

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?

Datatouille Established Member
Established Member

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 !

lcasey Member
Member

Re: Anyone Know how to Calculate a Perecentage?

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. 

 

 

 

Datatouille Established Member
Established Member

Re: Anyone Know how to Calculate a Perecentage?

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

 

 

Highlighted
lcasey Member
Member

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 ))

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 257 members 2,581 guests
Please welcome our newest community members: