Forum Discussion
Getting Difference of Data derived from the same table
Hi,
In Dynamic Query Mode, I have two tables that computer the median latency of certain jobs from the same table. They both have their own slicers so I can filter the two tables for the data I am looking for as such
I am wondering if it's possible to calculate the difference in the sum of the total median times? I.e. in example above the Difference would be -4.77
FYI
Courtesy of OwenAuger:
The formula for Median Latency =
VAR NumValuesHalved = COUNT ( query_timing_merged[Latency] ) / 2
RETURN
MINX (
FILTER (
VALUES ( query_timing_merged[Latency] ),
CALCULATE (
COUNT ( query_timing_merged[Latency] ),
query_timing_merged[Latency] <= EARLIER ( query_timing_merged[Latency] )
)
> NumValuesHalved
),
query_timing_merged[Latency]
)
and the formula for
Sum of Median Latency =
SUMX ( VALUES ( query_timing_merged[Query ID]), [Median Latency])
How do I calculate the difference between the two sums?
Per OwenAuger:
"Hi Artemis,
Sure, there are ways to do this.
Just checking your current setup: Is it correct that you just have one underlying table of data in the data model, and you have created two separate table visuals which are filtered by separate slicers which do not interact?
If that's the case, then we should actually change things. The options I can think of are:
- Create a 2nd copy of your data table, and create copies of the measures for that table. You would eventually end up with measures [Sum of Median Latency 1] and [Sum of Median Latency 2].
Then you can have Difference = [Sum of Median Latency 1] - [Sum of Median Latency 2] - Create a set of secondary lookup tables for any slicers you want to use, with inactive relationships to the corresponding columns in your main table. Then write a set of secondary measures that look like = CALCULATE ( [Original Measure], ALL ( <filter column 1> ), USERELATIONSHIP( <filter column 1>, <secondary filter column 1>,...)
This method is a bit more complicated.
Any chance you can post a link to a sanitised model - then I could build a quick example?
Regards,
Owen"
I went ahead with the first solution and it works!
- Create a 2nd copy of your data table, and create copies of the measures for that table. You would eventually end up with measures [Sum of Median Latency 1] and [Sum of Median Latency 2].
1 Reply
Per OwenAuger:
"Hi Artemis,
Sure, there are ways to do this.
Just checking your current setup: Is it correct that you just have one underlying table of data in the data model, and you have created two separate table visuals which are filtered by separate slicers which do not interact?
If that's the case, then we should actually change things. The options I can think of are:
- Create a 2nd copy of your data table, and create copies of the measures for that table. You would eventually end up with measures [Sum of Median Latency 1] and [Sum of Median Latency 2].
Then you can have Difference = [Sum of Median Latency 1] - [Sum of Median Latency 2] - Create a set of secondary lookup tables for any slicers you want to use, with inactive relationships to the corresponding columns in your main table. Then write a set of secondary measures that look like = CALCULATE ( [Original Measure], ALL ( <filter column 1> ), USERELATIONSHIP( <filter column 1>, <secondary filter column 1>,...)
This method is a bit more complicated.
Any chance you can post a link to a sanitised model - then I could build a quick example?
Regards,
Owen"
I went ahead with the first solution and it works!
- Create a 2nd copy of your data table, and create copies of the measures for that table. You would eventually end up with measures [Sum of Median Latency 1] and [Sum of Median Latency 2].