Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Adam_089
Regular Visitor

Problems displaying a normal time series

Hi,

 

I have the following information and just want to get it displayed as a normal time series. With the date on the X axis and the value on the Y, but I am getting a really random line graph out of it.

 

DateCosts
01.01.202331,50
01.02.202363,00
03.03.202363,65
21.02.202365,25
13.03.202366,35
13.03.202367,25
09.03.202368,65
09.03.202370,05
09.03.202371,45
09.03.202372,85
09.03.202374,25
21.03.202375,25
21.03.202376,25
28.03.202377,25
28.03.202378,25
21.04.202378,90
21.04.202379,44
21.04.202379,47
21.04.202379,55
21.04.202379,55
28.04.202380,55
28.04.202381,55
28.04.202382,55
28.04.202383,55
28.04.202384,55
28.04.202385,55
28.04.202386,55

 

And the graph which I get from this is shown like this :

Adam_089_0-1685518159207.png

I have no idea where it's getting these numbers from, I have turned off the summarization to "don't summarize" but yet it seems to keep on giving me a running total, but with a blip in the middle (probably because there are many entries for that specific date, but then how can I get it that it just takes the maximum and ignores the others ?) I would expect it to be a straight upwards trend with no random up and down in the middle. Any help with this would really be appreciated ! 😊

 

 

 

 

 

1 ACCEPTED SOLUTION
DOLEARY85
Super User
Super User

Hi,

 

try using a measure for the costs column:

 

Measure  = CALCULATE(MAX('Table Name'[Costs]),ALLEXCEPT('Table Name','Table Name'[Date]))
 
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

View solution in original post

10 REPLIES 10
aggiebrown
Helper III
Helper III

Hi @Adam_089,

 

It is best to create a measure, that will calculate your cost. I imagine you have a Date Table (where you are taking you're months from also).

 

Simple measure like this should work: 

Cost = CALCULATE(SUM(pbi[Costs]))
 
If you then drop your month and the measure "Cost" - the graph should look the way you want to.
Screenshot 2023-05-31 093839.jpg
Relationship between Date Calendar and your Cost Table:
 
Screenshot 2023-05-31 093915.jpg
Hope this helps 😀
Adam_089
Regular Visitor

Thanks, it's changed it, but now it's just basically a flat line over the time period only showing the maximum value and for dates in the future, which I shouldn't really show

 

Adam_089_0-1685520025539.png

 

 

That's strange, this is what i got with the same measure:

 

DOLEARY85_0-1685520309840.png

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

I've uploaded the file here:

 

https://drive.google.com/file/d/1cTlCF5kTFCG8FJ_e6hPOANzXC0UtMX5D/view?usp=sharing

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Ok, thanks, I don't quite know why, but when I deleted the graph and then reinserted it with exactly the same figures, it seems to have done more or less what I wanted to. Adam_089_0-1685524452771.png

 

The only challenge now is to make the X Axis display properly. If I put it on anything less than a year in the date hierarchy it shows me a daily breakdown (that's on the monthly option on the hierarchy) any idea on how to change this to be just back to the months ? I can't see an option in the formatting

Adam_089_1-1685524637011.png

 

 

 

 

I've noticed that sometimes the chart automatically drills down to the lowest level when deleting part of the hierachy. see if there's a drill up option on the chart.

 

If not delete the day level and only leave month that should work

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

You can use arrows up and down as highlighted to toggle between daily view and monthly view, depending on what you put on your X-Axis. Hope this helps.

Screenshot 2023-05-31 102426.jpg

Ah, doh ! Thanks, I totally forgot about that ! 😁

 

No worries! Glad it helped! 😀

DOLEARY85
Super User
Super User

Hi,

 

try using a measure for the costs column:

 

Measure  = CALCULATE(MAX('Table Name'[Costs]),ALLEXCEPT('Table Name','Table Name'[Date]))
 
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.