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

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

 

 

 

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!

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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.

    • YavuzDuran's avatar
      YavuzDuran
      Helper III
      5 years ago

      "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 : 

      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

       
      • YavuzDuran's avatar
        YavuzDuran
        Helper III
        5 years ago

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