Forum Discussion
Line Graph
I have data for sites for H1 H2 of each year scoring 0 to 3 named as Low, Medium, High,Critical, So everytime site record is captured should always fall between 0 to 3 score. I need graph which shows No of sites in H1 H2 of each year falling under category, but if a site falling under category of 2 in H2 and doesnot consist data for H1 then by default the line for H1 should start from 0 .
Hi PRAVINV - create a measure will count sites by Risk_Level while filling in missing data with zeros.
SitesCount =
VAR SelectedYearHalf = SELECTEDVALUE('Data'[AssessmentYear])
VAR SelectedRiskLevel = SELECTEDVALUE('Data'[Risk_Level])
RETURN
CALCULATE(
DISTINCTCOUNT('Data'[Site_Name]),
'Data'[AssessmentYear] = SelectedYearHalf,
'Data'[Risk_Level] = SelectedRiskLevel
) + 0add this measure in your y-axis .
reg. tooltip, we have tooltip section of your visual, add Site_Name.Ensure the Y-Axis is set to the SitesCount measure, not to Site_Name directly.In the Format Your Visual pane, under Y-Axis, disable Concatenate Labels and Show all items (if applicable).
Hope this works in your case.
7 Replies
Hi PRAVINV - Can you please share some sample data for reference and expected output?
Business Region Site_Name Scores Risk_Level AssessmentYear XYZ EEE Site1 1 Medium 2024 - H1 XYZ EEE Site1 0 Low 2024 - H2 XYZ EEE Site1 2 High 2025 - H1 ABC DDD Site2 0 Low 2024 - H1 ABC DDD Site2 2 High 2024 - H2 ABC DDD Site2 1 Medium 2025 - H1 Data is as above
Also how can I have only sites reflecting as tooltip at each datapoint, cause for me list of all sites on Y axis is reflecting
Hi PRAVINV - create a measure will count sites by Risk_Level while filling in missing data with zeros.
SitesCount =
VAR SelectedYearHalf = SELECTEDVALUE('Data'[AssessmentYear])
VAR SelectedRiskLevel = SELECTEDVALUE('Data'[Risk_Level])
RETURN
CALCULATE(
DISTINCTCOUNT('Data'[Site_Name]),
'Data'[AssessmentYear] = SelectedYearHalf,
'Data'[Risk_Level] = SelectedRiskLevel
) + 0add this measure in your y-axis .
reg. tooltip, we have tooltip section of your visual, add Site_Name.Ensure the Y-Axis is set to the SitesCount measure, not to Site_Name directly.In the Format Your Visual pane, under Y-Axis, disable Concatenate Labels and Show all items (if applicable).
Hope this works in your case.
working perfect, Thank you very much for the graph.
I have custom tooltip created as above for site name but on line graph it shows all the sites on Y axis for all Risk Level
