Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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)
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
@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.
"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
% =
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)
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |