Totals

[karunrathi]

 

 Hi All,
How do I fix my visual 2 - with lots of 1s into how it is visual 1.
I know in Power Query, we can group by, and merge the rows by dates. 
But this happened as I was building 2 reports simultaneously.. on one screen I added a matrix and the no.of transactions were showing as totals for that day for different locations. 

Then on the other, it is simply counting the occurences and not showing total for that day in the row. I tried copying the visual from one report to the other to see if it will come through looking identical but it didn't work. The data table is identical for both these reports. 

This is a snapshot of same time period on both reports, as you can see all the totals are matching. None of the settings, data formats, nor the data types are different, everything is identical thus far, any clue? 

I know it's something quite simple, I just think I'm overworked, and the solution is staring right back at me! Haha! 😂

Hi, @OwenAuger How's it going? It was so random, saw you were just online, out of thousands online, your profile photo popped up in Online section. Maybe if you see this, you can chip in to save me like old days? 

[karunrathi]

I guess a better question is, why is it working in 1 and not working in 2?  Ceterus Paribus! 
I haven't used a measure in image 1. 

[OwenAuger · Accepted solution]

Hi @karunrathi 

Good to see you again 😃

This is just a hunch, but it looks like in #2, TRANDATE may be a datetime column, formatted as a date. So each row of the visual corresponds to a different datetime value.

Could you check if the column is defined as type date in Power Query? Switching the type to Date after loading to the model can make the column appear as a date but the time part is still present.

That's what I can suggest off the top of my head, but if you can share a PBIX I could look further.

regards

Owen

[karunrathi]

It worked,, Thank you! 
Your hunch was 'Good as gold' 😄🙌 Is that how they say it down under??

I did flip that back and forth between Date and Date/Time today to try to fix it, but I kept doing it in the data view within the model. I changed the data type to DATE in Power Query for Test Report 1 yesterday while setting up the dataflow, I wanted to get the desired stack bar chart to work from these values - and it did work.

But forgot to do it today when I loaded the larger dataset for Report 2. And those hidden time stamps were driving me crazy, as I said, it felt like deja vu... similar thing happened 2 years ago, and I felt the solution was staring straight at me and you were the saviour back then as well. 

Thanks again! Hope you're well.