Forum Discussion
sum two scatter plots
Hi all,
I need to create a dashboard originated from a sql database. The structure of the DB is not very functional for the work I would like to do.
In the first column I have a time series in the format dd/mm/yyyyy hh:mm:ss, in the second a "tag" identifying a unique id and in the third a numeric value for each time step.
The table has this structure.
Date-time TagId Value Tag Name Tag Description
17/08/2022 09:05:00 4530 17,748 ARC RT RETE
17/08/2022 09:05:00 3345 6,765 ARC PZ via Leopardi
17/08/2022 09:00:00 3345 6,858 ARC PZ via Leopardi
17/08/2022 09:00:00 4530 17,436 ARC RT RETE
17/08/2022 08:55:00 4530 17,91 ARC RT RETE
17/08/2022 08:55:00 3345 8,19 ARC PZ via Leopardi
17/08/2022 08:50:00 3345 5,835 ARC PZ via Leopardi
17/08/2022 08:50:00 4530 17,616 ARC RT RETE
17/08/2022 08:45:00 4530 18,066 ARC RT RETE
17/08/2022 08:45:00 3345 7,071 ARC PZ via Leopardi
17/08/2022 08:40:00 3345 6,504 ARC PZ via Leopardi
17/08/2022 08:40:00 4530 17,7 ARC RT RETE
The tags I have are thousands and considering a data every 5 minutes the amount of data to handle is huge, in fact I work in direct query.
I would like to process such data and aggregate them and display the data with line or scatter plots, with on the x axis the date time field and on y the sum between one or more tags. The choice of which tags to use for my budget I do through a filter as in the image below.
If it was that I only have to add up the tags I can handle it without problems with the formula CALCULATE(SUM.....)
The problem arises when I have to do for example TAG 1 + TAG 2 - TAG 3, i.e. when I enter a subtraction, from the filter I cannot tell it which tag to subtract.
In my ignorance I thought of linking the sql database twice. In the first link I would select the tags to be considered positive and in the second the negative ones and then I would do something like : CALCULATE(SUM[table of positives] - SUM[TABLE OF NEGATIVES].
Conceptually it is right but I do not get the scatter plot showing the trend of the sum/difference of the tags. I attach photo below
In the example it is simplified to two tags, however in the top graph I have the values to add and in the bottom one the values to subtract. In the middle I should have the result obtained with the formula approximated above. The result I get is in fact the sum of all the data included in the time frame. The problem I realized is the time scale. So I tried to load a time series I created on xls and create a relationship between the one I created and the two time scales from the sql db (not sure if I explained myself)
I hope you can help me, I'm struggling but I'm not very handy on this sw.
Thanks
3 Replies
Hi AlexT86 ,
Have you tried making a formula similar to the one below:
Total values = CALCULATE ( SUM(Table[Column]), Table[Tag] = "Tag 1") +CALCULATE ( SUM(Table[Column]), Table[Tag] = "Tag 2") -CALCULATE ( SUM(Table[Column]), Table[Tag] = "Tag 3")This will force the calculaiton to make the sum based on the tag.
Is this your issue or you cannot identify the specifi tag you have has negative.
Hi MFelix ,
A tag can be positive or negative depending on the analysis I want to do . For example for one calculation it is positive but for the calculation after it is negative.
I want to choose to via the two filters I set up.I will try to attach the example.
I think the formula I set up is right, but the solution is in defining an x time axis common to the two filters.Hi,
Sorry for the additional questions, but in the example you have attached how is the setup of the negative an positive tags? Can you guide me trough the steps that makes the selection one or another?
Thanky you and once again apologize the additional questions.
