Total % calculation

[Boycie92]

Hi,

I am hoping some can help me.

I have two data tables.  A lookup table called Areas. In this there are two fields, Area and Building.

I have another table called Daily Task. In this table is a field called Building (that I use to establish a relationship with the lookup table) and additional fields called Total Tasks and Total Task Completed.

I want to be able to calculate the percentages of all tasks completed. I have the following formula:

Daily Tasks Completed = CALCULATE(SUM('Daily Tasks'[Total Completed Tasks]) / CALCULATE(SUM('Daily Tasks'[Total Tasks])))

The problem I have is that at any point in the day a record could be created for a building and then throughout the day the task completion numbers will change.

At the moment only two records have been created but I know there are ten buildings in total and that a record will be created for them by the end of the day.

My calculation works fine at an individual row level. However, my Total % is not representative of all 10 buildings.

For Example:

Building                Total Tasks          Total Task Completed                                 %

Building 1                            21                           5                                              23.81

Building 2                            21                           3                                              14.29

Total % 19.05 (This is visualised as a score card)

How do I make my Total % calculation as a representation of all 10 buildings and not just the ones that I have currently got records for?

Thanks,

Boycie92

[amitchandak · Accepted solution]

@Boycie92 , Not very clear. If is just a display, make sure you have building dimension and use show item with no data.

If you want to divide my number of buildings. Again you need a separate building dimension

Distinctcount(Building[Building])

[amitchandak]

@Boycie92 , I think your formula should work for 10 buildings. can share the problem answer and what you need

Daily Tasks Completed =

Divide( CALCULATE(SUM('Daily Tasks'[Total Completed Tasks]) , CALCULATE(SUM('Daily Tasks'[Total Tasks]))))

[Boycie92]

Hi @amitchandak 

The formula will work for the 10 buildings once all of the records have been created. However, what I need is to be able to trick Power BI into thinking all buildings have a record just now, even though there are only two.

This way my total % will be based on the 10 buildings and not the 2 records I currently have. Is that possible?

Thanks,

Boycie92

[Fowmy · Accepted solution]

@Boycie92 

What would be the total task for buildings that are not in the Daily Task table? is it 21 for all? If so, try the following:

Daily Tasks Completed =
Divide(
    CALCULATE(
        SUM('Daily Tasks'[Total Completed Tasks]),
        (COUNTROWS(values(AREAS[Builsing])) * 21
)

[amitchandak · Accepted solution]

@Boycie92 , Not very clear. If is just a display, make sure you have building dimension and use show item with no data.

If you want to divide my number of buildings. Again you need a separate building dimension

Distinctcount(Building[Building])