related multiplication

[Anonymous]

Hi all,

I am analyzing a market with five regions, and I have various tables:

- a table [DISPATCHLOAD] for the production [TOTALCLEARED] of different units (DUID)

- a table [DISPATCHPRICE] for the price in each region (note that, each time interval has 5 different prices [tradingRRP] according to the REGIONID)

- a table [DUIDTOTAL] with the details of the units registered, which gives information of the region of each units

They are, of course, related (I hope properly!)

I would like to add to the first table [DISPATCHLOAD] a column with the revenues of the given unit in the given interval.

Wich means, I need to perform a multiplication between the [TOTALCLEARED] quantity and the corresponding [tradingRRP], where the price must be the one corresponding to the same period [SETTLEMENTDATE] and region [REGIONID] (of course, this relation passes through the register of the units).

How can I do this?

Thanks!

[v-xicai]

Hi @Anonymous ,

You may create column or measure like DAX below, assuming there have been a relationship on DISPATCHLOAD[SETTLEMENTDATE] and DISPATCHPRICE[SETTLEMENTDATE], and another relationship on DISPATCHPRICE[REGIONID] and DUIDTOTAL[REGIONID].

Column1 =
CALCULATE (
    SUM ( DISPATCHLOAD[TOTALCLEARED] ) * SUM ( DISPATCHPRICE[tradingRRP] ),
    FILTER (
        DISPATCHLOAD,
        DISPATCHLOAD[SETTLEMENTDATE] = EARLIER ( DISPATCHPRICE[SETTLEMENTDATE] )
    ),
    FILTER (
        DISPATCHPRICE,
        DISPATCHPRICE[REGIONID] = EARLIER ( DUIDTOTAL[REGIONID] )
    )
)


Measure1 =
CALCULATE (
    SUM ( DISPATCHLOAD[TOTALCLEARED] ) * SUM ( DISPATCHPRICE[tradingRRP] ),
    FILTER (
        DISPATCHLOAD,
        DISPATCHLOAD[SETTLEMENTDATE] = MAX ( DISPATCHPRICE[SETTLEMENTDATE] )
    ),
    FILTER ( DISPATCHPRICE, DISPATCHPRICE[REGIONID] = MAX ( DUIDTOTAL[REGIONID] ) )
)

If I misunderstood it, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.

Best Regards,

Amy 

Community Support Team _ Amy

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

[Anonymous]

Hi @v-xicai Amy, thanks for you answer.

Maybe I didn't explained well. Here's the desired output:

It could perfectly be a column in the DISPATCHLOAD table where the revenue is computed as the product of the TOTALCLEARED for the corresponding tradingRRP for the given region (not shown the link in the image, but the price multiplied is the one corresponding to the region of the DUID) and the given SETTLEMENTDATE.

what you suggested gives me a column of a single value (maybe because the sums are multiplied by each other, and the multiplication is not performed row by row).

thanks