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 ta...
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
)

     




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
)

     




  • amitchandak's avatar
    amitchandak
    Super User
    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])