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

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

ie. For Row = Feb, Column = 5 total closed = 6, and you will see total sales = 69 in the second table 

so I need to calculate = 6/69 = %8.7

 

I will appreciate your help

 

 

YavuzDuran_0-1619620375682.png

 

1 ACCEPTED SOLUTION

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)

v-kelly-msft_0-1619777151198.png

For the related .pbix file,pls see attached.

 


Best Regards,
Kelly

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

View solution in original post

5 REPLIES 5
amitchandak
Super User IV
Super User IV

@YavuzDuran , You need to create a common date table and analyze it together.

 

divide(count(Table[account]) , sum(Table2[sales]))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

"Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s."

I have already done this, created a calender and put all relationships

and calculated a measure 

% =

SUM('Loan Performance'[Closed ?])/count('Loan Performance'[sales])
But this gave me the following : 
YavuzDuran_0-1619622163829.png

which is wrong

 

I need the percentage of the closed accounts / total sales (fixed for selected month)

 

so the numbers will be

1%

3%

2%

5% soething like this 

not that much in above table

 

count('Loan Performance'[sales]) 

I need to free this denominator from the column parameters (1,2,3,..)

Should only be affected by the row values (Jan, Feb, Mar,...)

In other words, 

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

 

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)

v-kelly-msft_0-1619777151198.png

For the related .pbix file,pls see attached.

 


Best Regards,
Kelly

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

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors