cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stuartcoggins
Frequent Visitor

Grand total varies depending on fields used in Rows

Hi there,

 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:

 

stuartcoggins_0-1646321549807.png

 

 

 

When I drag PaymentCategory onto Rows, the grand total remains the same which is good:

 

stuartcoggins_1-1646325112268.png

 

 

But when I drag CloseDate onto Rows instead, the grand total goes down a lot:

 

stuartcoggins_2-1646325166253.png

 

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!

Many thanks,

Stuart

3 REPLIES 3
MFelix
Super User
Super User

Hi @stuartcoggins ,

 

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi Miguel,

 

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!

Many thanks,
Stuart

Hi Stuart,

 

As refered without any additional information it's rely hard, context transiction may be happening that it may not realize on the images you provide.

 

If you want to send some more details on this you can send me a PM and we can try and check this.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.