Calculations that account for zero entries and roll up the hierarchy.

[A_Scott]

My locations have a goal of 1 inspection per day. I’m report on a rolling 7 days. How do I calculate the percentage complete where the percentage doesn’t get inflated when a location completes more than 1 on any day but doesn’t complete any on other days?

Here is my example. Location #530 completed a total of 7 inspections. However, they only completed them 5 out of 7 days. Calculation of # of inspections divided by goal of 7 would show them at 100% compliant. But they really are not because the goal is 1 per day, and they have 2 days with zero completed. So they should show about 71%. I also need this calculation to rollup to the District (07) and the Division (035). Also, need to cap the percentage at 100%.

This is how I would do it in Excel:

I can not figure out how to duplicate the Percent Complete in Power BI and it properly roll up to District and then Division.

Here is my count of days with >0 inspections per day. You can see because I’m using DistinctCount, it is not rolling up to the District and Division properly.

Once the count of stores and days with inspections is configured, I will also have to calculate the percentage completed. In the example above, it is returning nothing.

[v-pgoloju]

Hi @A_Scott,

ust following up to see if the solutions provided by community members were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[v-pgoloju]

Hi @A_Scott,

Just following up to see if the responses provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

Best regards,

Prasanna Kumar

[v-pgoloju]

Hi @A_Scott,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @ashleyfiore  and @FBergamaschi  for their prompt and helpful responses.

Just following up to see if the solutions provided by community members were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[ashleyfiore · Accepted solution]

You need three measure to do this function. 

Days with Inspections :=
CALCULATE(
    COUNTROWS(
        SUMMARIZE(
            Inspections,
            Inspections[LocationID],
            Inspections[InspectionDate]
        )
    ),
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -7, DAY)
)

Expected Inspection Days :=
CALCULATE(
    COUNTROWS(
        SUMMARIZE(
            ADDCOLUMNS(
                VALUES('Date'[Date]),
                "Dummy", 1
            ),
            'Date'[Date]
        )
    ),
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -7, DAY)
)
*
DISTINCTCOUNT(Inspections[LocationID])

% Inspection Compliance (Capped) :=
VAR DaysCompleted = [Days with Inspections]
VAR DaysExpected = [Expected Inspection Days]
VAR RawPct = DIVIDE(DaysCompleted, DaysExpected)
RETURN
    MIN(RawPct, 1)

[FBergamaschi]

The technique is to force the calculation of the nr of inspections to be carried out for each day. So if you consider a week, it does not simply sum the nr of inspections for the dates in that week but it does that one day at a time. So, if you find any day in which it is less than 1, you can consider that.

To help you more,  please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI