Forum Discussion

JBikker's avatar
JBikker
Regular Visitor
8 years ago
Solved

Costs / daycount

Hi there, I'm just starting with Power BI and can't figure out the following.   I have a daycount colomn and a costs colomn. I want to device costs by total daycount. After that I want to multiply...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    8 years ago

    Hi JBikker,

     

    Here we can take the following steps to meet your requirement.

     

    1. Add a calculated column to get the sum of Daycount per contract.

     

    Sum = CALCULATE(SUM(Subscriptions[Daycount]),FILTER(ALL(Subscriptions),Subscriptions[Part of contract]=EARLIER(Subscriptions[Part of contract])))

    2. Then can need to update the formula as below.

     

    Result = RELATED(Contracts[Costs 2018]) / Subscriptions[Sum] * Subscriptions[Daycount]

     

    For more details, please check the pbix as attached.

     

    https://www.dropbox.com/s/ofd8q15osojrozr/prev2.pbix?dl=0

     

    Regards,

    Frank

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
8 years ago

Hi JBikker,

 

As your description, I made a sample here. As you said Costs 2018 is a column in another query. To achieve your goal here, the two tables should be related. So I created relationship between the DATA and cost tables based on index columns in advance.

 

 

Then we can create the calculate column in Data table using the formula.

 

Result = RELATED(Cost[Cost 2018])/SUM(DATA[Daycount])*DATA[Daycount]

Then we can get the result as we excepted.

 

 

For more details, please check the pbix as attached. If this doesn’t meet your requirement, kindly share you sample data to me please.

 

https://www.dropbox.com/s/401oxqjtl2uq4jv/Costsdaycount2.pbix?dl=0

 

Regards,

Frank

  • JBikker's avatar
    JBikker
    Regular Visitor
    8 years ago

    v-frfei-msftThanks so much for your help! I'm getting closer.  I have two followup questions.

     

    1. How can you calculate daycount if there is no enddate yet? If the enddate is blank I would like to get the result Startdate > Today

    2. 'Price' is not always the same. Subscriptions are part of different contract.  When I used your method in my model it did not filter per contract but instead summed up all 'daycount's from different contracts.

     

    I've attached a file.. Hopefully this makes it a bit more clear. The column result does not make any sense.

     

    https://wetransfer.com/downloads/2c34615a0ebc0ae4241048b8e0e3fc5d20180807152428/d0cb3c5ac7d1392ce5ee08c9ba984e6f20180807152428/856d47

    • v-frfei-msft's avatar
      v-frfei-msft
      Icon for Community Support rankCommunity Support
      8 years ago

      Hi JBikker,

       

      Here we can take the following steps to meet your requirement.

       

      1. Add a calculated column to get the sum of Daycount per contract.

       

      Sum = CALCULATE(SUM(Subscriptions[Daycount]),FILTER(ALL(Subscriptions),Subscriptions[Part of contract]=EARLIER(Subscriptions[Part of contract])))

      2. Then can need to update the formula as below.

       

      Result = RELATED(Contracts[Costs 2018]) / Subscriptions[Sum] * Subscriptions[Daycount]

       

      For more details, please check the pbix as attached.

       

      https://www.dropbox.com/s/ofd8q15osojrozr/prev2.pbix?dl=0

       

      Regards,

      Frank

      • v-frfei-msft's avatar
        v-frfei-msft
        Icon for Community Support rankCommunity Support
        8 years ago

        Hi JBikker,

         

        Does that make sense? If so, kindly mark my answer as a solution to close the case please.

         

        Regards,

        Frank