cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Post Prodigy

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

1 ACCEPTED SOLUTION
MVP

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
MVP

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 !

Post Prodigy

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?

MVP

@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 !

Post Prodigy

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.

MVP

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

Post Prodigy

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

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors