Forum Discussion

YavuzDuran's avatar
YavuzDuran
Helper III
5 years ago
Solved

Percentage Calculation between two tables

Hello all, I have two tables 1 - The closed accounts in each month (The column shows the 1st, 2nd, etc month) 2 - Total Sales for each month   and I need to put a table showing the percentage i...
Need Help
Tips and Tricks
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  YavuzDuran ,

     

    Be sure that in both 2 tables have the column of MonthNo;

    In your calendar table,also create a MonthNo column;

    Then create a measure as below:

    Measure = 
var _closedaccounts=CALCULATE(SUM('Table (2)'[Value]),FILTER(ALL('Table (2)'),'Table (2)'[Column]=MAX('Calendar'[MonthNo])&&'Table (2)'[Row]=MAX('Table (2)'[Row])))
var _totalsales=CALCULATE(SUM('Table'[# of Acts]),FILTER(ALL('Table'),'Table'[MonthNo]=MAX('Calendar'[MonthNo])))
Return
DIVIDE(_closedaccounts,_totalsales)

    And you will see:(here I only take part of your sample data)

    For the related .pbix file,pls see attached.

     


    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

YavuzDuran's avatar
YavuzDuran
Helper III
5 years ago

In other words, 

I am trying to find the percentage of total row (total row of the second table (sales))

 

v-kelly-msft's avatar
v-kelly-msft
Community Support
5 years ago

Hi  YavuzDuran ,

 

Be sure that in both 2 tables have the column of MonthNo;

In your calendar table,also create a MonthNo column;

Then create a measure as below:

Measure = 
var _closedaccounts=CALCULATE(SUM('Table (2)'[Value]),FILTER(ALL('Table (2)'),'Table (2)'[Column]=MAX('Calendar'[MonthNo])&&'Table (2)'[Row]=MAX('Table (2)'[Row])))
var _totalsales=CALCULATE(SUM('Table'[# of Acts]),FILTER(ALL('Table'),'Table'[MonthNo]=MAX('Calendar'[MonthNo])))
Return
DIVIDE(_closedaccounts,_totalsales)

And you will see:(here I only take part of your sample data)

For the related .pbix file,pls see attached.

 


Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!