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.
Hello,
I have a need to calculate % of revenue . I tried using the Default % of Grand total , but since there are negative numbers it calculates revenue past 100%
Revenue should alsways = 100% and the percentage should be based on Revenue.
How Can I calculate the Percentage of Revenue ?
Hi @lcasey,
Could you try using SUMX function(DAX) to calculate the Amount, then use the Default % of Grand total to see if it works? The formula below is for your reference.
Amount = SUMX ( '00-GLSummary', IF ( '00-GLSummary'[Revenue] > 0, [Credit] - [Debit], [Debit] - [Credit] ) )
Regards
I Tried , but the numbers are now wrong. The Bottom Line number should
Using SUMX Incorrect Bottom Line
Correct Bottom Line:
How about this?
% of Revenue = DIVIDE ( SUM ( Table[Amount] ), CALCULATE ( SUM ( Table[Amount] ), Table[PLTYPE] = "Revenue", ALLSELECTED ( Table[PLCategory] ) ), 0 )
Or you can try ALL(Table) instead of ALLSELECTED ( Table[PLCategory] )
Good Luck!
There is also no Sum of Amount there are only debits and credits.
My Amount must be a measure to make revenue show as a positive number:
Amount = IF(SUM('00-GLSummary'[Revenue]) > 0,[Credit] - [Debit],[Debit] - [Credit])
Revenue = IF(RELATED('00-COASlicer'[PLTYPE]) = "Revenue" ,1,0)
How about this? Just substitute the real table name where PLCategory is (I could not see it)
% of Revenue = DIVIDE ( [Amount], CALCULATE ( [Amount], '00-COASlicer'[PLTYPE] = "Revenue", ALLSELECTED ( 'Table'[PLCategory] ) ), 0 )
I have tried that and there is no errors now, but no calculation either.
See Picture:
Also,
If I model the % of Revenue as a % then Expenses when filtered show the wrong amount....
Hello Sean,
It doesnt seem to work. I am getting an Incorrect Syntax error.
Maybee its the relationship?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |