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, Revenu...
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

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

       

       

      • FuzzyYabs's avatar
        FuzzyYabs
        Frequent Visitor
        7 years ago

        Hello v-cherch-msft ,

         

        here's a Sample file. 

         

        There are two datasets. Individual and Total. 

        The need is to create a third table, which will have the computation as below.