How to sum the values and divide with total values

[Anonymous]

Hi

Here, in date column it has generated on daily basis and the map date should generated for last third day. Finally I have to get the total sum for occupied and count and divide the total value (count/occupied) and create a new column as derived column.

Am facing issue to create a Derived column.

Regards,

Yuvaraj

[v-juanli-msft]

Hi @Anonymous

If you table is like this, all columns already exsits like below in your dataset except "column"

then you can create a column using LivioLanzo's formula.

If you dataset isn't like this, please let me know.

Best Regards

Maggie

[LivioLanzo · Accepted solution]

Hello @Anonymous,

you can add it like this:

=
IF(
    NOT( ISBLANK( Data[Mapdate] ) ),
    CALCULATE(
        DIVIDE(
            SUM( Data[occupied] ),
            SUM( Data[count] )
        ),
        Data[date] >= EARLIER( Data[date] ) - 2,
        Data[date] <= EARLIER( Data[date] ),
        ALL( Data )
    )
)

[Anonymous]

Hi LivioLanzo,

Thanks for the reply.

Yes its working fine. I have another doubt similar to this, consider if i am having two tables(Table 1: Mapdate,Count ; Table 2: date,occupied) like below screens how we will join the Mapdate and date column and do the same calulation which we did earlier.

Regards,

Yuvaraj

[v-juanli-msft]

Hi @Anonymous

What is the first table actually?

From your screenshot, it seems it have applied some options like "Merge cells".

Does your table look like this after "unmerged cells"?

Mapdate	Count
12/4/2018	8
12/4/2018	6
12/4/2018	2
12/7/2018	10
12/7/2018	10
12/7/2018	8
12/10/2018	15
12/10/2018	15
12/10/2018	15

Best Regards

Maggie

[Anonymous]

Hi

Yes I have appiled merge center but it wont repeat the values to clear on this i have make it as zero. Please refer the below screenshot and let me know if anything required.

Regards,

Yuvaraj 

[v-juanli-msft]

Hi @Anonymous

In Queries Editor,

Copy table1, in Table1_copy, “Group by”,

in Table2, merge queries, then  expand all

In Table2, click on the “down-arrow” of the “date” field name, sort ascending.

Then click on “Table 1 _copy.Map date”, select Fill->Up,

Also fill up  for the column “Table 1 _copy.count”

Close&&Apply

Create columns

Column = CALCULATE(SUM('Table 2'[occupied]),ALLEXCEPT('Table 2','Table 2'[Table 1 _copy.Map date]))

Column 2 = [Column]/[Table 1 _copy.count]

Best Regards

Maggie