Forum Discussion

lcasey's avatar
lcasey
Icon for Post Prodigy rankPost Prodigy
9 years ago

Reverse Revenue and Keep the Correct NET Value

Hello,   Has anyone reversed revenue in Power BI?   I am migrating all SSRS Great Plains Financial reports over to Power BI.  Already, Power BI has proved to be faster, and allow the organization...
Need Help
v-caliao-msft's avatar
v-caliao-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi lcasey,

 

My goal would be to make the revenue negative number be a positive number but not affect the overall net amount calculated in the Table or Matrix.

 

In DAX, you could you ABS function to make the negative number to a positive number. ABS function returns the absolute value of a number. However, the total row is calculated automatically, we cannot change the expression on total row, so if you make the negative number to a positive number, it seems that it will effect total row.

 

Referecnce
https://msdn.microsoft.com/en-us/library/ee634566.aspx?f=255&MSPPError=-2147217396

 

Regards,

Charlie Liao

  • lcasey's avatar
    lcasey
    Icon for Post Prodigy rankPost Prodigy
    9 years ago

    I have found a solution to Reversing Revenue.

     

    For anyone struggling with this using the Great Plains General Ledger, here is what I did:

     

    Created Calculatd column to Identify Revenue within my GL table :

     

    Revenue = IF(RELATED('00-COASlicer'[PLTYPE]) = "Revenue" ,1,0)

     

    I created 3 Measures to Handle Debit , Credit and Amount:

     

    Credit = Sum('00-GLSummary'[CRDTAMNT])

     

    Debit = SUM('00-GLSummary'[DEBITAMT])

     

    Amount  = IF(SUM('00-GLSummary'[Revenue]) > 0,[Credit] - [Debit],[Debit] - [Credit])