I have some aggregate data by month that is categorized by location, demographic variable such as gender and race. the data looks like month, location, metric, demographic, numerator, denominator 1/1/2021 Hosp A ED Male 2 20 1/1/2021 Hosp B ED Male 3 18 1/1/2021 Hosp C ED Male 0 10

I have data like this for female, white, black, hispanic, and other demographic variables.

I need to create a line chart in power bi that has 4 lines per site. Rate per 10,000 (num/denom * 10000). The average rate per 10000 for the first 24 months. an upper control limit and a lower control limit.

The issue is that whenever I put my rate and average and ucl and lcl lines in the y axis, I cannot put the demographic or the site fields in my legend. it only lets me put anything in the legend if i only have one field in the y axis.

I also have to be able to compare one hospital with the other hospitals. so for example, i should be able to see rate per 10,000 male for hosp a and rate per 10000 male for hosp b on the same graph. Right now I am having to create a separate measure for each and every single combination (hosp A + male) (hosp A + female) ( Hosp b plus male) (hosp B plus female) etc. and i have to create a rate for each combination. an average for each combination. and upper and lower control limits for each combination and it is taking forever and making my report really slow.

I have tried a combo line chart and everything I Can think of but the legend field doesnt work whenever I put more than one field in the y axis.

can someone please help?

I have some aggregate data by month that is categorized by location, demographic variable such as gender and race. the data looks like month, location, metric, demographic, numerator, denominator 1/1/2021 Hosp A ED Male 2 20 1/1/2021 Hosp B ED Male 3 18 1/1/2021 Hosp C ED Male 0 10

I have data like this for female, white, black, hispanic, and other demographic variables.

I need to create a line chart in power bi that has 4 lines per site. Rate per 10,000 (num/denom * 10000). The average rate per 10000 for the first 24 months. an upper control limit and a lower control limit.

The issue is that whenever I put my rate and average and ucl and lcl lines in the y axis, I cannot put the demographic or the site fields in my legend. it only lets me put anything in the legend if i only have one field in the y axis.

I also have to be able to compare one hospital with the other hospitals. so for example, i should be able to see rate per 10,000 male for hosp a and rate per 10000 male for hosp b on the same graph. Right now I am having to create a separate measure for each and every single combination (hosp A + male) (hosp A + female) ( Hosp b plus male) (hosp B plus female) etc. and i have to create a rate for each combination. an average for each combination. and upper and lower control limits for each combination and it is taking forever and making my report really slow.

I have tried a combo line chart and everything I Can think of but the legend field doesnt work whenever I put more than one field in the y axis.

can someone please help?

• powerbi
• dax

Share Follow asked Mar 6 at 19:26 AviAvi 1 3

• Have you tried creating another line chart with most of its componentry hidden (e.g. labels, legends, and axis lines) and transparent, and then positioning that new line chart on top of the existing? – Mike G Commented Mar 10 at 12:14
• no I am not sure how to do that. Are you suggesting that I create 4 line charts each with its own line and then putting them on top of each other? – Avi Commented Mar 18 at 14:58
• Yes. I am suggesting that you try it to see if it satisfies the requirement. – Mike G Commented Apr 2 at 11:15

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

I have a work around, don't use the built in confidence limits or use the demographic or site fields but create a dax measures for each one and filter in the measure. The measure name will then appear in the legend.
For the rates something like this should do for a Males in Hospital A

RatePer1000_HospA_Male = 
VAR FilteredNumerator =    CALCULATE(       SUM(YourTable[numerator]),       YourTable[location] = "Hosp A",       YourTable[demographic] = "Male"   ) 

VAR FilteredDenominator =    CALCULATE(       SUM(YourTable[denominator]),       YourTable[location] = "Hosp A",       YourTable[demographic] = "Male"   ) 

RETURN    
IF(FilteredDenominator > 0, (FilteredNumerator / FilteredDenominator) * 1000, BLANK())

I have not used this but there is function called CONFIDENCE.T(alpha,standard_dev,size) that looks like it might work for you.

I suspect that the confidence limits will change given the sample size in each location so you might want to consider a different format to share the data