Forum Discussion

Boycie92's avatar
Boycie92
Resolver I
5 years ago
Solved

Total % calculation

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

Need Help
Show and Tell
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    5 years ago

    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])

     

     

  • Fowmy's avatar
    Fowmy
    5 years ago

    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
)

     




4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      Boycie92
      Resolver I
      5 years ago

      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's avatar
        Fowmy
        Super User
        5 years ago

        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
)