Forum Discussion

FuzzyYabs's avatar
FuzzyYabs
Frequent Visitor
7 years ago
Solved

Create a calculation based on two tables

Hello Power Bi Gurus! 

 

Would need your help on something. 

I currently have two tables, Revenue Individual (1) and Revenue Collection (2). I need these two tables to create a third table, Revenue Rate (3). 

 

The formula for the third table is a  follow: 

Rev Rate = indivudual Revenue on the month / total 12months Revenue Collection (fixed 12months not dynamic)

 

You can see from the image below. 

 

Nov 2017 rate = 179 / Sum (Nov 2017 to Oct 2018)

Dec 2017 rate = 117 / Sum (Nov 2017 to Oct 2018)

 

 

 

as any of you could have guessed, there is a specific 12months. And that 12months sum will be used to calculate the rate. 

 

can anyone teach me how to do this?  Currently have no clue on how to. 

 

 

Need Help
Tips and Tricks
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi FuzzyYabs 

     

    Please check the measures in attachment.

    AC Power Rate = 
DIVIDE (
    CALCULATE ( SUM ( Individual[Amount] ), Individual[Department] = "AC POWER" ),
    CALCULATE (
        SUM ( Total[Total] ),
        FILTER (
            ALL ( Total ),
            Total[Department Total] = "AC POWER"
                && Total[Date] <> "NOV C18"
        )
    )
)

    Regards,

    Cherie

9 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi FuzzyYabs 

     

    You may transpose the two tables in query editor.Then you may create measures and use matrix visual to get the rate.Attached the simplified sample for your reference.

    SumRevenueCollection = SUMX('Revenue Collection','Revenue Collection'[Revenue Collection])
    Revenue Rate = SUM('Revenue Individual'[Revenue Individual])/[SumRevenueCollection]

    Regards,

    Cherie

    • FuzzyYabs's avatar
      FuzzyYabs
      Frequent Visitor
      7 years ago

      Hi v-cherch-msft, 

       

      Thank yuo! but I think I would like to apologize for this but I think I have vaguely used the term table. 

       

      Tables 1 and 2 are two visualizations. 

      Table 1 was created using "Individual" as row and "Months" as columns.

      Likewise, Table 2 was created using "Total Revenue" as row and "Months" as columns.

       

       

       

      • v-cherch-msft's avatar
        v-cherch-msft
        Microsoft Employee
        7 years ago

        Hi FuzzyYabs 

         

        You may just create the measures as below.If it is not your case,please share the .pbix sample file for us to provide an accurate solution.You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

        SumRevenueCollection = SUMX('Revenue Collection','Revenue Collection'[Revenue Collection])
        Revenue Rate = SUM('Revenue Individual'[Revenue Individual])/[SumRevenueCollection]

        Regards,

        Cherie