Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

related multiplication

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!

 

 

Need Help
Tutorial Requests

2 Replies

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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