Subtracting rows and produce a table showing combination result

[berjac]

Hi community

I wonder if anyone is able to help me on what i thought would be quite simple to solve but i cant seem to find a solution.

I have a table of transaction showing tenant consumption within a building.  The table also contains a total for the whole building [INCOMER]

TABLE 1
Detail	kWh	Category
Tenant 1	100	Tenant
Tenant 2	150	Tenant
Tenant 3	50	Tenant
Tenant 4	25	Tenant
Tenant 5	20	Tenant
Tenant 6	10	Tenant
Tenant 7	300	Tenant
Tenant 8	75	Tenant
Tenant 9	70	Tenant
BUILDING TOTAL	1000	Incomer

What i am trying to produce is a table that shows the total for the tenants and includes another total for the difference between the Incomer and the sum of all the tenants.  See table below:

RESULT REQUIRED
Detail	kWh
Building Total UNRECOVERED (B-A)	200
Tenant 1	100
Tenant 2	150
Tenant 3	50
Tenant 4	25
Tenant 5	20
Tenant 6	10
Tenant 7	300
Tenant 8	75
Tenant 9	70
BUILDING TOTAL	1000

Can anyone please assist?

[Greg_Deckler]

@berjac Seems like it the value for that other line would be something like:

Table (11a) = 
    VAR __Table1 = SELECTCOLUMNS(FILTER('Table (11)',[Category] = "Tenant"),"Detail",[Detail],"kWh",[kWh])
    VAR __Table2 = SELECTCOLUMNS(FILTER('Table (11)',[Category] = "Incomer"),"Detail",[Detail],"kWh",[kWh])
    VAR __Table3 = 
        SELECTCOLUMNS(
            { ( "Building Total UNCRECOVERED (B-A)", SUMX(__Table2,[kWh]) - SUMX(__Table1,[kWh]) ) },
            "Detail",[Value1],
            "kWh",[Value2]
        )
RETURN
    UNION(__Table3,__Table1,__Table2)

[berjac]

@Greg_Deckler  - Thanks for the prompt response.

Is it possible that i can attach a copy of the PBIX file for you to take a look at ..... It seems to give me a problem when i try your formula?

[Greg_Deckler]

@berjac Here is the PBIX file I used attached below sig. If you do not have rights to attach PBIX files, you will need to put it on OneDrive or Box or something and share a link to it.

[berjac]

@Greg_Deckler 

[berjac]

@Greg_Deckler 

Ok .... I got that to work just perfectly ..... You're a genius !!  However, the formula you provided works perfectly if it is only one utility on a single building for a single month.

I have added some additional data to the table to show both water and electricity as well as a building Number and a date.

When i use the formula you provided it sums up all the differences into 1 line item.

Is it possible to use the same method to work out an incomer difference for both water and electricity for each building for each month?

I hope this makes sense?

PS - note the table labels have changed slightly 

Kind regards

B

[Greg_Deckler]

@berjac I can't get to that PBIX file:

[berjac]

@Greg_Deckler 

Hi Greg

Did you manage to download the file?

Regards

Bernard

[berjac]

@Greg_Deckler 

Try this one:   PBIX FILE DOWNLOAD 

[berjac]

Ok .... I got that to work just perfectly ..... You're a genius !!  However, the formula you provided works perfectly if it is only one utility on a single building for a single month.

I have added some additional data to the table to show both water and electricity as well as a building Number and a date.

Detail                Qty    Meter     Building Category    Date

Tenant 1	100	Tenant	1	Electricity	31/01/2020
Tenant 2	150	Tenant	1	Electricity	31/01/2020
Tenant 3	50	Tenant	1	Electricity	31/01/2020
Tenant 4	25	Tenant	1	Electricity	31/01/2020
Tenant 5	20	Tenant	1	Electricity	31/01/2020
Tenant 6	10	Tenant	1	Electricity	31/01/2020
Tenant 7	300	Tenant	1	Electricity	31/01/2020
Tenant 8	75	Tenant	1	Electricity	31/01/2020
Tenant 9	70	Tenant	1	Electricity	31/01/2020
BUILDING TOTAL	1000	INCOMER	1	Electricity	31/01/2020
Tenant 10	75	Tenant	2	Electricity	31/01/2020
Tenant 11	25	Tenant	2	Electricity	31/01/2020
Tenant 12	33	Tenant	2	Electricity	31/01/2020
Tenant 13	67	Tenant	2	Electricity	31/01/2020
Tenant 14	100	Tenant	2	Electricity	31/01/2020
Tenant 15	25	Tenant	2	Electricity	31/01/2020
Tenant 16	200	Tenant	2	Electricity	31/01/2020
Tenant 17	150	Tenant	2	Electricity	31/01/2020
Tenant 18	80	Tenant	2	Electricity	31/01/2020
BUILDING TOTAL	1000	INCOMER	2	Electricity	31/01/2020
Tenant 1	10	Tenant	1	Water	31/01/2020
Tenant 2	15	Tenant	1	Water	31/01/2020
Tenant 3	5	Tenant	1	Water	31/01/2020
Tenant 4	2.5	Tenant	1	Water	31/01/2020
Tenant 5	2	Tenant	1	Water	31/01/2020
Tenant 6	1	Tenant	1	Water	31/01/2020
Tenant 7	30	Tenant	1	Water	31/01/2020
Tenant 8	7.5	Tenant	1	Water	31/01/2020
Tenant 9	7	Tenant	1	Water	31/01/2020
BUILDING TOTAL	100	INCOMER	1	Water	31/01/2020
Tenant 10	7.5	Tenant	2	Water	31/01/2020
Tenant 11	2.5	Tenant	2	Water	31/01/2020
Tenant 12	3.3	Tenant	2	Water	31/01/2020
Tenant 13	6.7	Tenant	2	Water	31/01/2020
Tenant 14	10	Tenant	2	Water	31/01/2020
Tenant 15	2.5	Tenant	2	Water	31/01/2020
Tenant 16	20	Tenant	2	Water	31/01/2020
Tenant 17	15	Tenant	2	Water	31/01/2020
Tenant 18	8	Tenant	2	Water	31/01/2020
BUILDING TOTAL	100	INCOMER	2	Water	31/01/2020
Tenant 1	100	Tenant	1	Electricity	29/02/2020
Tenant 2	150	Tenant	1	Electricity	29/02/2020
Tenant 3	50	Tenant	1	Electricity	29/02/2020
Tenant 4	25	Tenant	1	Electricity	29/02/2020
Tenant 5	20	Tenant	1	Electricity	29/02/2020
Tenant 6	10	Tenant	1	Electricity	29/02/2020
Tenant 7	300	Tenant	1	Electricity	29/02/2020
Tenant 8	75	Tenant	1	Electricity	29/02/2020
Tenant 9	70	Tenant	1	Electricity	29/02/2020
BUILDING TOTAL	1000	INCOMER	1	Electricity	29/02/2020
Tenant 10	75	Tenant	2	Electricity	29/02/2020
Tenant 11	25	Tenant	2	Electricity	29/02/2020
Tenant 12	33	Tenant	2	Electricity	29/02/2020
Tenant 13	67	Tenant	2	Electricity	29/02/2020
Tenant 14	100	Tenant	2	Electricity	29/02/2020
Tenant 15	25	Tenant	2	Electricity	29/02/2020
Tenant 16	200	Tenant	2	Electricity	29/02/2020
Tenant 17	150	Tenant	2	Electricity	29/02/2020
Tenant 18	80	Tenant	2	Electricity	29/02/2020
BUILDING TOTAL	1000	INCOMER	2	Electricity	29/02/2020
Tenant 1	10	Tenant	1	Water	29/02/2020
Tenant 2	15	Tenant	1	Water	29/02/2020
Tenant 3	5	Tenant	1	Water	29/02/2020
Tenant 4	2.5	Tenant	1	Water	29/02/2020
Tenant 5	2	Tenant	1	Water	29/02/2020
Tenant 6	1	Tenant	1	Water	29/02/2020
Tenant 7	30	Tenant	1	Water	29/02/2020
Tenant 8	7.5	Tenant	1	Water	29/02/2020
Tenant 9	7	Tenant	1	Water	29/02/2020
BUILDING TOTAL	100	INCOMER	1	Water	29/02/2020
Tenant 10	7.5	Tenant	2	Water	29/02/2020
Tenant 11	2.5	Tenant	2	Water	29/02/2020
Tenant 12	3.3	Tenant	2	Water	29/02/2020
Tenant 13	6.7	Tenant	2	Water	29/02/2020
Tenant 14	10	Tenant	2	Water	29/02/2020
Tenant 15	2.5	Tenant	2	Water	29/02/2020
Tenant 16	20	Tenant	2	Water	29/02/2020
Tenant 17	15	Tenant	2	Water	29/02/2020
Tenant 18	8	Tenant	2	Water	29/02/2020
BUILDING TOTAL	100	INCOMER	2	Water	29/02/2020

When i use the formula you provided it sums up all the differences into 1 line item.

Is it possible to use the same method to work out an incomer difference for both water and electricity for each building for each month?

I hope this makes sense?

PS - note the table labels have changed slightly 

Kind regards

B

Download PBIX file