Weird Grand Total issue

[KUNGFUPANDA559]

Weird situation.  I have 2 measures. 

1. Total Points := Calculate(Sum(Points),All(Test[Jerseys])

2.  Points by Jersey := Sum(Points)

When i filter on jersey 0 

1.=143,715

2.=53636

When i filter on jersey 14

1.=90,443

2.=81,286

My thoughts are by adding the "All(Test[jersey]" filter that is that all values, regardless of jersey, should be added in 1.  what am i doing wrong?

[Phil_Seamark]

HI @KUNGFUPANDA559

Is your filter actually using the [Jerseys] column from the 'Test' table?  or are you using a related column from a different table for your filter?

[KUNGFUPANDA559]

@Phil_Seamark

I only have one table of data.  So i don't believe that'd be possible

[Phil_Seamark]

Hi @KUNGFUPANDA559

As far as I can see you have your measure right.  I've replicated here in this simple Power BI Desktop model and it's working fine

https://1drv.ms/f/s!AtDlC2rep7a-ogQled8k3zPewMZw

Are you using Excel or Power BI Desktop?

[KUNGFUPANDA559]

@Phil_Seamark

i'm using power bi desktop.  just when i thought i was understanding things (sighs)

[Phil_Seamark]

Hi @KUNGFUPANDA559,

You understand things correctly but there is something else at play.  Did you try out the small model I built for you?

[KUNGFUPANDA559]

@Phil_Seamark

So i'm 100% positive that i've found the issue.  I'm just unsure of how to go about fixing it.  The data is being filtered (via DirectQuery) on dates that are formatted as Date/Time values.  When filtered by page slicer (01/08/2018 to 01/14/2018), all of the data is not being included because it does not "equal" the true date value.  Any thoughts on conversion? or next steps to take?

[Phil_Seamark]

What is your data source?  MS SQL Server?

[Anonymous]

@Phil_Seamark

I had this same issue, although involving other data sources (Excel, CSV), in several occasions.

I normally solved it by converting the columns to Date in the Query Editor.

I remember one case where I had to set it to Date/Time/Timezone to make it work.

In another case, I needed to preserve the time stamp in the original column, and had to use a calculated column that returned the date portion only.

Let me know if this helps.

[KUNGFUPANDA559]

@Phil_Seamark

I'm using direct query (MS Server 2008).  

@Anonymous

We're definitely on the right track.  Typically i'd use the Convert() function to change formatting of column.  It's not playing nice with direct query mode and if i proceed i will lose directquery functionality

[v-huizhn-msft]

Hi @KUNGFUPANDA559,

Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. More people will benefit from here, and we can close the thread.

Best Regards,
Angelia

[v-huizhn-msft · Accepted solution]

Hi @KUNGFUPANDA559,

When you use Direct Query, the date format is same as in SQL server database, you can add a custom column in Query Editor to convert your date column as posted in this case. Or you can try  to Use Locale for Date Conversion, please review "Date Conversion Using Locale" part in this blog.

Best Regards,
Angelia

[KUNGFUPANDA559]

Sorry had to put the kiddos to bed.  I downloaded the example you provided.  My measure is configured exactly like the one you've defined. However' i'm still having funky calculations.  I'm using DirectQuery to retrieve data.  I'm curious if there some sort of relationship that exists that may be effecting results?!?

[Ashish_Mathur]

Hi,

Share the link from where i can download your data.