Trying to build a table based on multiple lists that contains calculated values from other lists

[godinbl]

I apologize in advance I am a newbie to Power Bi but I have done a lot of searching this forum and Youtube videos and can't seem to find the answer to my problem:

I have created a table in Power Bi based on the following Sharepoint Lists:

Meter Readings

Auths

AEs

SWANs

The common field in all these lists is called "auth" and the relationships are many to many - I have created those already

The table is primarily based on Meter Readings and I can pull data fields in from the other lists as you can see from my screenshot but I need to create a calculated column called "Entitlement" which should be:
Auths[volumeML]+SWANs[SWAN]*AEs[AE]+Auths[S&D]+Auths[PA]

I also need to group the table by auth - not sure of the best way to achieve that - any help greatly appreciated  thanking you 🙂

[Ashish_Mathur]

Hi,

Share some data to work with, explain the question and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[godinbl]

My table - based primarily on Meter Readings Sharepoint List:

Entitlement = volumeML + amount * thisyear + S&D + PA

Expecteds result from calculations involving red text fields:

Auth	ClientName	ClientRef	WMA	Zone	Entitlement
123456	Joe Bloggs	435621	Fitzroy	Zone 1	57
234567	Bill Nobody	657891	Comet	Zone B	104
987654	Mrs Brown	87654	Isaac Connors	Zone 12	33.5

Auths List:

Auth	ClientName	volumeML	S&D	PA
123456	Joe Bloggs	50	2	5
234567	Bill Nobody	40	2	0
987654	Mrs Brown	20	2	16.5

SWANs List:

Auth	ClientName	amount
123456	Joe Bloggs	0
234567	Bill Nobody	80
987654	Mrs Brown	0

AEs List:

Auth	ClientName	thisyear
123456	Joe Bloggs	1
234567	Bill Nobody	0.85
987654	Mrs Brown	0.75

[v-xinruzhu-msft]

Hi,

Thanks for the solution godinbl offered, and i want to offer some more information for user to refer to.

hello @godinbl , based on the function you have offered, the entitlement for 234567 should be 110 and the entitlement for 987654 should be  38.5 you can refer to the following calculated column.

Column =
VAR _auth = [Auth]
VAR _sumauthslist =
    CALCULATE (
        SUM ( 'Auths List'[volumeML] ) + SUM ( 'Auths List'[S&D] )
            + SUM ( 'Auths List'[PA] ),
        'Auths List'[Auth] = _auth
    )
VAR _amo =
    CALCULATE ( SUM ( 'SWANs List'[amount] ), 'SWANs List'[Auth] = _auth )
VAR _thisyear =
    CALCULATE ( SUM ( 'AEs List'[thisyear] ), 'AEs List'[Auth] = _auth )
RETURN
    _sumauthslist + _amo * _thisyear

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[godinbl]

Hi @v-xinruzhu-msft thanks for your response but my calculations are correct - perhaps you misunderstood the logic or I did not explain properly:

The calculation is: Entitlement = volumeML + amount * thisyear + S&D + PA

For auth 234567 the calculation is as follows:

Entitlement = 40 + 80 * 0.85 + 2 + 0 and the answer is 104

For auth 987654 the calculation is:

Entitlement = 20 + 0 * 0.75 +2 + 16.5 and the answer is 33.5

The order that the calculation is carried out is important to the final figure so it has to be left to right as seen above.

hope this clarifies things a little

thanks again for your help!

[v-xinruzhu-msft]

Hi @godinbl 

Thanks for your quick reply, do you mean  (40 + 80) * 0.85 + 2 + 0  and   (20 + 0) * 0.75 +2 + 16.5 , you can refer to the following.

Column =
VAR _auth = [Auth]
VAR _vol =
    CALCULATE ( SUM ( 'Auths List'[volumeML] ), 'Auths List'[Auth] = _auth )
VAR _amo =
    CALCULATE ( SUM ( 'SWANs List'[amount] ), 'SWANs List'[Auth] = _auth )
VAR _thisyear =
    CALCULATE ( SUM ( 'AEs List'[thisyear] ), 'AEs List'[Auth] = _auth )
VAR _sumsd_pa =
    CALCULATE (
        SUM ( 'Auths List'[S&D] ) + SUM ( 'Auths List'[PA] ),
        'Auths List'[Auth] = _auth
    )
RETURN
    ( _vol + _amo ) * _thisyear + _sumsd_pa

Output

Best Regards!

Yolo Zhu

[godinbl]

thank you very much for your solution to my previous problem - I have one more thing to add to the calculations:

I am filtering my report on a field called "readdate" from the MeterReadings list and I have added a Slicer to the page so I have a datepicker that the user can choose between 2 dates for their report. Essentially the user is looking for Meter Readings where the readdate is between these 2 dates.

The SWANs list also has a date field; in this case 'SWANs'[expirydate] which should also be BETWEEN my start and end dates in the Slicer

How can I add a little bit of code to this line: 

VAR _amo =
    CALCULATE ( SUM ( 'SWANs List'[amount] ), 'SWANs List'[Auth] = _auth )

so that the SWANs[amount] will only be included in the report when the expirydate is between start and enddate?

thanks

[v-xinruzhu-msft · Accepted solution]

Hi @godinbl 

You can try the following.

CALCULATE (
    SUM ( 'SWANs List'[amount] ),
    'SWANs List'[Auth] = _auth,
    'SWANs List'[expirydate] >= MIN ( 'MeterReading'[readdate] ),
    'SWANs List'[expirydate] <= MAX ( 'MeterReading'[readdate] )
)

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[godinbl]

thank you so much for assistance provided so far - could you possibly help with one more thing:

I have to group the report by the "auth" field and I have done this by adding the auth field to a new table and putting this code in: 

Table2 = SUMMARIZE('MeterReadings','MeterReadings'[auth])

However, when I calculate the usage I need to group by "meterref" from the MeterReadings table so that I take the difference between the MAX and MIN "exactreading", GROUPBY "meterref" and then SUM all values to get the "Usage" figure

The MeterReadings table looks like this:

MeterRef	Auth	readdate	exactreading
17811	1137AP6829	15/06/2023	4,958.89
17811	1137AP6829	17/06/2024	6,157.09
17812	1139AP6829	15/06/2023	16,394.65
17812	1139AP6829	17/06/2024	19,752.88
17871	1139AP6829	15/06/2023	3,161.49
17871	1139AP6829	17/06/2024	620.53
17871	1139AP6829	16/01/2024	0

Expected results are:

1137AP6829 Total Usage = 1198.20

1139AP6829 Total Usage = 6519.72

This is what the Usage calculation is currently:

Usage = CALCULATE((MAX(MeterReadings[exactreading])-(MIN(MeterReadings[exactreading]))),'MeterReadings'[readdate] >= MIN ( 'MeterReadings'[readdate] ),

    'MeterReadings'[readdate] <= MAX ( 'MeterReadings'[readdate] ))

thanking you again...

[godinbl]

thank you!

[Ashish_Mathur]

Hi,

Using the LOOKUPVALUE() function in calulcated column formulas, bring over the red coloured columns from the last 3 tables into the first table.  Thereafter, create another calculated column formula for your final calculation.