cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
FuzzyYabs Frequent Visitor
Frequent Visitor

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)

 

 

Revenue.JPG

 

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. 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-cherch-msft Super Contributor
Super Contributor

Re: Create a calculation based on two tables

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 REPLIES 9
v-cherch-msft Super Contributor
Super Contributor

Re: Create a calculation based on two tables

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.

1.png

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

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
FuzzyYabs Frequent Visitor
Frequent Visitor

Re: Create a calculation based on two tables

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 Super Contributor
Super Contributor

Re: Create a calculation based on two tables

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

 

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
FuzzyYabs Frequent Visitor
Frequent Visitor

Re: Create a calculation based on two tables

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. 

Sample.JPG

 

v-cherch-msft Super Contributor
Super Contributor

Re: Create a calculation based on two tables

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
FuzzyYabs Frequent Visitor
Frequent Visitor

Re: Create a calculation based on two tables

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. 

v-cherch-msft Super Contributor
Super Contributor

Re: Create a calculation based on two tables

Hi @FuzzyYabs 

 

What is a selected date range? You want to add a slicer to get the dynamic date range?If so,you may refer to below post to get the last 12 month values from selected month.

https://community.powerbi.com/t5/Desktop/Show-last-6-months-of-values-from-selected-month/m-p/619157...

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
FuzzyYabs Frequent Visitor
Frequent Visitor

Re: Create a calculation based on two tables

Hello @v-cherch-msft 

 

Your samples are very helpful. I will look into your suggestions. Thank you so much! 

v-cherch-msft Super Contributor
Super Contributor

Re: Create a calculation based on two tables

Hi @FuzzyYabs 

 

Could you tell me if your problem has been solved? If it is,kindly mark the helpful answer as a solution and welcome to share your own solution. More people will benefit from here. If not, please share more details for us so that we could help further on it.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 170 members 2,225 guests
Please welcome our newest community members: