Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Average based on drill down or up when using a measure

I want to get the people that are ill visualized in a line graph diagram. Currently I can visualize the correct numbers for each day. Although when I drill up to the week level or even month level I get the sum of the people that are sick for every day. Which is correct but the issue is that people are often sick for more then 1 day which results in number that are inaccurate on week level or month level (or year). So the possible solution could be to take the average but I don’t know how to do this.

This is the measure I use to get the amount of people that are sick for each day. 

sickness for each day=

var table1 = filter(ADDCOLUMNS(

            Fact,

            "Sick",

            if(RELATED(Dim_Sick) = "Sick", "1", "0")

            ), [Sick] = "1")

 

var countr = COUNTROWS(table1)

RETURN

CALCULATE(

        countr,

        Fact

        )

 

Needs to show the average amount of people per week and per month

2 Replies

  • Hello

     

    try to use "isinscope"

    ex: if(isnscope([day], [day measure], [month measure])

     

    Did i answer your question? Please mark it as solution!

    • Anonymous's avatar
      Anonymous
      Not applicable

      I actually don't know how to create the month measure for it. I know how to get the count of sick people per day but I don't know how to show the average of ill people per week or per month. Do you have any suggestions based on my measure I created above.