Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to Solution.
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 !
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 !
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?
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 !
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.
That's OK ! Happy to see it helped you 🙂
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 ))
User | Count |
---|---|
120 | |
95 | |
87 | |
73 | |
66 |
User | Count |
---|---|
138 | |
112 | |
110 | |
98 | |
94 |