Forum Discussion
Power BI Desktop with QuickBooks Online - Income vs. Sales
I have created a connection between Power BI Desktop and QuickBooks Online which brings several tables into Power BI. In QuickBooks I am interested in two reports, one about sales by client and one about income by client. These differ in that the income report factors in expenses. In Power BI, I have only been able to calculate the sales totals (sum of 'Invoice'[Total Amount] ). I would like to also show the income totals by client, but I have not been able to find a way to do this. Is there a way to isolate expenses in a Power BI table and calculate income? Or should this information be available in a table that can be imported? I have looked through every table I can import to try to identify something that indicates expenses related to a client but have not been able to.
Please let me know if you can help, thanks!
Hi nicky_b ,
Assuming that after importing all the data into Power BI, the result is as follows
You can create the following measure
NET INCOME = SUM('Table'[INCOME])+SUM('Table'[EXPENSES])Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
Hi nicky_b ,
You can try to create a measure using ALLEXCEPT function.
The measure is like
Measure = CALCULATE(SUM('Invoice'[Amount]),ALLEXCEPT('Invoice','Invoice'[Client]))Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This seems to calculate the sales total as I have been able to. The issue I'm facing is that there are separate sales and income reports in QuickBooks and I would like to show the totals from the income report in Power BI. The sales and income totals often differ due to the expenses being factored into the income total. Do you know if this is possible to show/calculate based the tables that can be pulled into Power BI from QuickBooks?
Hi nicky_b ,
Do you mean that you still have tables in QuickBooks that have not been imported into Power BI?
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Thejeswar
Super User
nicky_b ,
I have once come across a similar problem in sharepoint, where in I was able to see the columns in sharepoint but not in power bi when imported that data to Power BI.
Then found that the columns were displaying a name while the underlying name was differently stored in sharepoint and Power BI was showing the undelying name and not the display name.
Do check if there is something like this causing this issue (where you are able to see data in Quickbooks online but not in Power BI when the data is imported to) ?
