Creating A Simple graph using two columns of a Dataverse Table

[NigelP99]

Hi 

I have a very simple dataverse table consisting of a Date Time field "Created On" and a Numeric field called "Temperature".

I want PowerBI to have the "Created on" on the X-Axis and the "Temperature" on the Y-Axis.

PowerBI insists I want the "Sum Of" temperatures on the Y-Axis. I do not.

This should be just  case of dragging "Created on" onto the X-asxis and "Temperature" onto the Y axis and perhaps Filter on the last 24 hrs.

Why is this so difficult ?

Or is this a bug in PowerBI ?

Regards

Nigel

[bolfri]

Due to fact that you've not provided any sample data I've used my own.

Created on	Temperature (Celsius)
2022-01-01 12:00:00	22,5
2022-01-02 12:00:00	21,3
2022-01-03 12:00:00	19,8
2022-01-04 12:00:00	17,4
2022-01-05 12:00:00	14,2
2022-01-06 12:00:00	11,1
2022-01-07 12:00:00	8,4
2022-01-08 12:00:00	5,9
2022-01-09 12:00:00	4,2
2022-01-10 12:00:00	2,8
2022-01-11 12:00:00	-0,5
2022-01-12 12:00:00	-2,8
2022-01-13 12:00:00	-4,4
2022-01-14 12:00:00	-6,7
2022-01-15 12:00:00	-9,4
2022-01-16 12:00:00	-11,7
2022-01-17 12:00:00	-14,4
2022-01-18 12:00:00	-16,7
2022-01-19 12:00:00	-19,4
2022-01-20 12:00:00	-22,2

Step 1) Make sure that Created on is a date/datetime format and Temperature is numeric one.

Step 2) Choose Clustered column chart and drag Created on to X-axis and Temperature on Y-axis.

Note that my data is based on random values, so it looks silly, but it works OK 🙂

[NigelP99]

Thanks for your prompt response.  Here was what I was hoping to get (works from Excel!) 

This is what happens if I use a line chart in PowerBI

I dont understand why this works in Excel but not in PowerBI.

Regards

Nigel

[bolfri]

Remove Created on from legend.

Make sure to use dates on Y-axis, not only Time value.

[NigelP99]

Hi @bolfri 

Why use dates on Y axis ?

I want my date / time to run along the bottom (x-axis)

Regards

Nigel

[bolfri]

I am sorry, I meant x-axis. 🙂 Just make sure it's a datetime, because right now it seems to be a time only.

[NigelP99]

Hi

I think the issue is that PowerBI does not like the field (Actual Temperature) I am using. As PowerBI converts to Count Of when the field is a Text field. But I am using a Dataverse table where the field is of Type "Decimal" which PowerBI should not convert to a CountOf in the Y-Axis of my Line chart.

Any ideas ?

Regards

Nigel

[bolfri]

Yes. If it's decimal field then change Count to Average/Sum (as u prefer). It will work then.

[NigelP99]

Hi

I dont want Average / Sum - I just want the value of the field.

(or am I missing something ?)

Thanks

Nigel

[bolfri · Accepted solution]

Yes, you're missing the fact that in visual you need to give Power BI context of aggregation. In your case even if you will put the AVG/SUM to the field - it will return the value of the field.

SUM(17,2) is 17,2
AVERAGE(17,2) is still 17,2

Doesn't matter if you will use SUM or AVG - it will return its value.

[NigelP99]

Hi @bolfri

Thanks - thats great !¬

It now works as I wanted it to.

Thanks for staying with me on this one.

Thanks

Nigel