Percentage of Sales Question/Issue

[CodeBig]

Hey Everyone,

I have a question regarding creating a certain measure that I can display on a matrix widget in Power BI Desktop.

I am attempting to create a Percentage of Sales measure using table data from QuickBooks Online. 

This is what I have for my measure so far:

P&L - Sales = CALCULATE(SUMX(FILTER('GeneralLedger-Accrual-Account', 'GeneralLedger-Accrual-Account'[Account.1.Account Type] = "Income"), 'GeneralLedger-Accrual-Account'[P&L - Amount]))

% of Sales = DIVIDE(SUM('GeneralLedger-Accrual-Account'[P&L - Amount]), [P&L - Sales])

When I do this, I am able to get the correct % of Sale for accounts that have the Account Type of "Income", but when I try to add this column to an account that is not of type "Income", I just get an "Infinity" amount or a blank. It will only give me the correct % of Sales for the bottom grand row total of the Matrix widget.

I am sorry if I sound vague with this question. This is my first post I have made on a forum and can provide more information.

Here is a visual of what I have so far. I would like to make the accounts that are not in the 1:Sales Parent row also contain a % of Sales for the sum of the Sales for that period. Thank you all!

[v-xjiin-msft]

@CodeBig

Based on your expressions, you have defined a Filter in P&L - Sales. Which means you are only calculating the sum of [P&L - Amount] when [Account.1.Account Type] = "Income". When [Account.1.Account Type] equals to other values, the measure will not calculate it and returns blank.

Then in your second measure, you are doing something like: DIVIDE(SUM('GeneralLedger-Accrual-Account'[P&L - Amount]),BLANK()) when [Account.1.Account Type] is not "Income". That's why you are getting "Infinity" amount or a blank.

To calculate all the [P&L - Amount] based on [Account.1.Account Type] group, you can try this:

P&L - Sales =
CALCULATE (
    SUM ( 'GeneralLedger-Accrual-Account'[P&L - Amount] ),
    FILTER (
        ALL ( 'GeneralLedger-Accrual-Account' ),
        'GeneralLedger-Accrual-Account'[Account.1.Account Type]
            = MAX ( 'GeneralLedger-Accrual-Account'[Account.1.Account Type] )
    )
)

And if you want to only calculate some specific [Account.1.Account Type], you can try this:

P&L - Sales =
IF (
    MAX ( 'GeneralLedger-Accrual-Account'[Account.1.Account Type] ) = "Income",
    CALCULATE (
        SUMX (
            FILTER (
                'GeneralLedger-Accrual-Account',
                'GeneralLedger-Accrual-Account'[Account.1.Account Type] = "Income"
            ),
            'GeneralLedger-Accrual-Account'[P&L - Amount]
        )
    ),
    CALCULATE (
        SUMX (
            FILTER (
                'GeneralLedger-Accrual-Account',
                'GeneralLedger-Accrual-Account'[Account.1.Account Type] = "xxx"
            ),
            'GeneralLedger-Accrual-Account'[P&L - Amount]
        )
    )
)

If my expression doesn't satisfy your requirement. Please kindly share us some sample data and your desired result based on this sample data. So that we can make proper tests.

Thanks,
Xi Jin.

[CodeBig]

Thank you for your reponse Xi Jin,

I will try this today and report back!

[v-xjiin-msft]

Hi @CodeBig,

Did you resolve your issue?

Thanks,
Xi Jin.

[CodeBig]

Hey Xi Jin,

I was working on it a little bit last week but have lost track of time. I will be getting back to it very soon. 

I was using the QuickBooks Online connector for Power BI, but it is lacking some features that I needed to manage multiple QBO files so I may have to use an alternative or connect to the QBO SDK and build a custom solution