How to create a combined stats panel with single set of values from multiple data sources in Grafana

[sumachandrashekar]

• What Grafana version and what operating system are you using?
  I am using Azure managed Grafana(Grafana v10.4.7).

• What are you trying to achieve?
  I am currently building a Grafana dashboard using the mixed mode(from different Azure Monitor subscriptions). I have to add in a stats panel which contains two calculations(Success Request and Failure Request). These calculations need to change based on a region filter as shown below.

• How are you trying to achieve it?
  I have added two different queries(from Azure Monitor) one below the other to populate all the graphs whose values can be filtered based on the variables provided(region). Most visualizations work except for the stats visuals.

• What happened?
  The stats visual gives two different set of values.

• What did you expect to happen?
  Expected a single set of calculations(one set of Success Request and Failure Request) instead of two set of calculations shown. For example, the first screenshot has the region “China” selected in the variable drop-down and the numbers are shown on the right. When the filter is changed to another region, say “EMEA” the numbers populate on the left. I would like to have just one set of calculations shown instead of two? I cannot join the resources together outside of Grafana since I do not have permission.

• Can you copy/paste the configuration(s) that you are having problems with?
  *Screenshot 1
  

  

*Screenshot 2

The KQL query used:

StorageBlobLogs
| where Location == "${region}"
| summarize SuccessRequest = countif(StatusText == "Success" and UserAgentHeader == "LTA"), 
              FailureRequest = countif(StatusText == "AuthorizationFailure" and UserAgentHeader == "LTA")

• Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
  None
• Did you follow any online instructions? If so, what is the URL?
  None

[sowdenraymond]

try some of the transformations, use concat to join your two queries results, use binary operation if you want to add or subtract etc.

[sumachandrashekar]

Thank you for the guidance @sowdenraymond. I was able to merge the results into one set of values.
For anyone having a similar issue, I have extended the KQL query to include region and used “Series to rows” transformation to merge the two queries.
Screenshots below:

StorageBlobLogs
| where Location == "${region}"
| summarize SuccessRequest = countif(StatusText == "Success" and UserAgentHeader == "LTA"), 
              FailureRequest = countif(StatusText == "AuthorizationFailure" and UserAgentHeader == "LTA") by Location

Screenshot1: