Calculate % of Revenue

[lcasey]

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 ?

[v-ljerr-msft]

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

[lcasey]

I Tried , but the numbers are now wrong.  The Bottom Line number should

Using SUMX Incorrect Bottom Line

Correct Bottom Line:

[Sean]

@lcasey

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!

[lcasey]

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)

[Sean]

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
)

[lcasey]

I have tried that and there is no errors now, but no calculation either.

See Picture:

[lcasey]

Also,

If I  model the % of Revenue as a % then Expenses when filtered show the wrong amount....

[lcasey]

Hello Sean,

It doesnt seem to work. I am getting an Incorrect Syntax error.

Maybee its the relationship?