Calculate weekly reports based on daily submissions

[ttseng]

Hi,

I have a data set that provides daily reports from various facility. What i'm trying to get is to find out the number of sites that provided 5 reports that week. What i'm looking for is a bar graph with weeks as the X and the Y as the number of sites that provided 5 reports that week. This is what my data looks like:

Report	Facility	Week
daily	Site A	1
daily	Site A	1
daily	Site A	1
daily	Site A	1
daily	Site A	1
daily	Site B	1
daily	Site B	1
daily	Site B	1
daily	Site B	1
daily	Site B	1
daily	Site C	1
daily	Site C	1
daily	Site C	1
daily	Site C	1
daily	Site A	2
daily	Site A	2
daily	Site A	2
daily	Site B	2
daily	Site B	2
daily	Site B	2

I'm struggling to figure how how to do this with measures. 

Thank you!

[v-kelly-msft · Accepted solution]

Hi @ttseng ,

What you need is to create a measure as below:

total sites =
VAR _table =
    SUMMARIZE (
        'Table',
        'Table'[Facility],
        'Table'[Week],
        "Report1", COUNT ( 'Table'[Report] )
    )
VAR number =
    SUMX ( _table, [Report1] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Facility] ),
        FILTER ( _table, [Measure 2] = 5 )
    )

Finally you will see: 

For the related .pbix file,pls click here.

Best Regards,

Kelly

[ttseng]

Thank you @v-kelly-msft - this solution worked for me!

[ttseng]

Hi @v-kelly-msft ,

I want to thank you for providing the original solution. I've returned with another question. It's always the seemingly simple questiosn that baffle me.

This time I want to be able to return sites that did not submit data. So in your example it would be Measure 2 = 0. However, I have a suspicion that it's not possible. How would one go about solving such a problem? Create a master table to reference if Week and Facility is missing return 0? or something like that. 

In the end i want to be able to return facility, week number, and if data was submitted or not. 

Thank you again!

-Tony

[HotChilli]

You could put 'Week' on the axis, Count of Report in the Value well and Facility in the legend.

That will give you a clustered column chart with all data.

You could use the Visual filters to set a value on the Count of Report field (either = 5 or any other filter)

[HotChilli]