Grand total varies depending on fields used in Rows
I've got a confusing issue with my organisation's new set-up of Snowflake to Power BI.
Some context on the set-up that my colleague has recently carried out:
He's set up a PBIX file that contains data queries to Snowflake
This PBIX file has been published to a PBI workspace
We then either use Power BI Desktop or via app.powerbi.com to access the datasets within that workspace
I believe this is a live connection to the dataset as Desktop tells me that I am 'connected live to the Power BI dataset'. So it's not a DirectQuery connection but I have to admit I am not that clear on the difference!
So that is the context of our set-up. The problem I've had ever since then (in both Desktop and via app.powerbi.com) is that reports are not functioning as I'd expect. For example, in the below screenshot I am querying one of those published tables and the grand total income shows as I expect:
When I drag PaymentCategory onto Rows, the grand total remains the same which is good:
But when I drag CloseDate onto Rows instead, the grand total goes down a lot:
To be clear, I've not applied any filters in this step, I have only replaced PaymentCategory with CloseDate. The same thing happens with some other date fields but also other types of data, so it's not solely linked to date fields as far as I can tell.
The part that really confuses me is that when I look at Snowflake's query history, I can see that the query produced by the CloseDate example above gives me the correct grand total:
select "CloseDate", "C1" from ( select "CloseDate", sum("AmountIncludingGiftAid") as "C1" from "AnalysisTeam"."schema"."table_name" group by "CloseDate" ) as "ITBL" where not "C1" is null LIMIT 1000001 OFFSET 0;
As in, when I run this query separately in Snowflake, the grand total for AmountIncludingGiftAid is exactly what I expect. So it appears that some rows are somehow being lost/filtered out once the data gets into Power BI to be visualised.
I hope that's enough detail and makes sense. If anyone has any ideas I'd be very grateful!
Without knowing your model is difficult to pin point the error, since you are using dates on the visualization that is incorrect,.
do the date column comes from the same table?
If they come from the same table turn on the show items without data on the visualization (just to see if there are strange values like blanks or other things)
If it's from a different table, are you sure all values have a date to be related?
Also check how the relationships are setup.
Remember that context in Power BI and DAX are given by a large number of things not only filters.
To give you an example I add a case of a value that would also did not appear when applying a date but on the categories it was correct, when I looked at the model there was a bi-directional filter on a table that was related with the calendar that filtered out my calendar and my totals.
Not saying this is the case, but if you have the totals correct when there is split and when you do a calculation with a category or a date those change this is for sure related with the way the model is setup.
Did I answer your question? Mark my post as a solution!
Thank you for your reply. I can appreciate that it's difficult to solve this without being able to see the model set-up! The date column does come from the same table, so I assume that it can't be related to model set-up.
I checked the 'Show items without data' and it didn't appear to make a difference. And I checked the source data in our data warehouse and there don't appear to be any null values.
So thank you for you help with this but I appreciate it might be difficult to do anything further on this!