Calculate a hourly difference per dimensions

[Stella_V]

Hello 

I am trying to calculate a hourly difference, for the selected day in a date slicer, between two attributes.

More precisely I want to calculate th difference in "Value" between France and Austria, per hour on the same day, for the attribute  'forecast1'.

	Time	Hour	Attribute1	Attribute2	Value
D	24.10.2023	1.00	Austria	Forecast1	10
D	24.10.2023	2.00	Austria	Forecast1	20
D	24.10.2023	3.00	Austria	Forecast1	30
D	24.10.2023	4.00	Austria	Forecast1	40
D	24.10.2023	5.00	Austria	Forecast1	50
D	24.10.2023	1.00	France	Forecast1	25
D	24.10.2023	2.00	France	Forecast1	140
D	24.10.2023	3.00	France	Forecast1	60
D	24.10.2023	4.00	France	Forecast1	50
D	24.10.2023	5.00	France	Forecast1	65
	Result	hour	Attribute3	Attribute2	Value
	24.10.2023	1.00	France-Autria	Forecast1	15
	24.10.2023	2.00	France-Autria	Forecast1	120
	24.10.2023	3.00	France-Autria	Forecast1	30
	24.10.2023	4.00	France-Autria	Forecast1	10
	24.10.2023	5.00	France-Autria	Forecast1	15

Can you please help me? I did a direct query. 

thank you very much !

Stella

[barritown · Accepted solution]

Hi @Stella_V,

If you want to solve your task via a Table visual, you could create such a measure and use it:

In plain text:

NewValue = SUMX ( FILTER ( Data, [Attribute1] = "France" ), [Value] ) - SUMX ( FILTER ( Data, [Attribute1] = "Austria" ), [Value] )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[Stella_V]

It works perfectly!!! thank you!!!!!

[barritown · Accepted solution]

Hi @Stella_V,

If you want to solve your task via a Table visual, you could create such a measure and use it:

In plain text:

NewValue = SUMX ( FILTER ( Data, [Attribute1] = "France" ), [Value] ) - SUMX ( FILTER ( Data, [Attribute1] = "Austria" ), [Value] )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[Stella_V]

Hello 

thank you very much for your answer. 

I forgot to mention you that there are several different attributes in the column "Attribute 2" for example: 

Hour 1 Forecast1

Hour 2 Forecast1

Hour 3 Forecast1

Hour 4 Forecast1

Hour 1 Forecast2

Hour 2 Forecast2

Hour 3 Forecast2

Hour 4 Forecast2

Hour 1 Forecast3

Hour 2 Forecast3

Hour 3 Forecast3

Hour 4 Forecast3

Should we add an additional filter on the Attribute2? What  I need is the hourly difference between two countries, per forecast type.

thank you !!
Stella

[barritown · Accepted solution]

@Stella_V,

This approach will work with multiple Forecasts (Forecast1, Forecast2, etc.) - I dare you to try. 🙂 Should you face any issues, let me know.

>>Should we add an additional filter on the Attribute2?

There is no need to apply additional filtering.

Cheers,

Alexander