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

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. 

     

    • v-cherch-msft's avatar
      v-cherch-msft
      Microsoft Employee
      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

      • FuzzyYabs's avatar
        FuzzyYabs
        Frequent Visitor
        7 years ago

        Hi v-cherch-msft 

         

        Almost! haha however, how am I suppose to make the calculation such that the totals will only be for a selected date range? ex. the total should be the sum from Nov 2017 - Oct 2018??? 

         

        Apologies for having to many questions about this and thank you for helping me on this.