Measure adding monthly values

[AbrarBMC]

I have 5 years of monthly data with the following columns: Date, Total Users, Active users. I tried to calculate active user ratio using a measure. The measure calculates the monthly values correctly, but when I use the date hierarchy and use year only, the measure adds the monthly values instead of recalculating yearly values. Can anyone help?

Date	Total Users	Active Users
01/01/2019	10000	2000
02/01/2019	11000	2200
03/01/2019	12000	2600
04/01/2019	13000	2500
05/01/2019	14000	3000
06/01/2019	15000	2700
07/01/2019	16000	4000
08/01/2019	17000	3750
09/01/2019	18000	4250
10/01/2019	19000	4000
11/01/2019	20000	5000
12/01/2019	20000	5000
01/01/2020	21000	5500

Sample formula that I was using for the measure: 

Active Users Ratio = Table[Active Users] / Table[Total Users]

[v-rzhou-msft]

Hi @AbrarBMC 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[amitchandak]

@AbrarBMC ,

Not very clear

try like

AverageX(Summarize(Table, Table[Month-year], "_1" ,sum(Table[Total Users]), "_2" ,sum(Table[Active users])), divide([_2],[_1]))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Greg_Deckler]

@AbrarBMC Sorry, having trouble following, can you post sample data as text and expected output?

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[AbrarBMC]

I have updated my post and added a table and the formula I was using. I want the output to be active user ratio. I want it in two timeframes. Monthly basis and yearwise. Let me know if you need more details.

[v-rzhou-msft · Accepted solution]

Hi @AbrarBMC 

I think you may build a calculated column except a measure, so when you change the Date to Date Hierachy (Only Year) you will get the sum of the rate.

I build a table like yours to have a test.

Calculated column:

Column = 'Table1'[Active Users]/'Table1'[Total Users]

Measure:

Active Users Ratio = DIVIDE(SUM('Table1'[Active Users]), SUM('Table1'[Total Users]))

Result:

We can see the measure's result is correct and the calculated column's result is wrong like yours.

Or you can build two measures to see the result directly.

I add a Year column and Month column in Table2.

Year = YEAR(Table2[Date])

Month = MONTH(Table2[Date])

Measure:

Monthly Active Users Ratio = DIVIDE(SUM(Table2[Active Users]),SUM(Table2[Total Users]))

Yearwise Active Users Ratio = 
var _AU = CALCULATE(SUM(Table2[Active Users]),FILTER(ALL(Table2),Table2[Year]=MAX(Table2[Year])))
var _TU = CALCULATE(SUM(Table2[Total Users]),FILTER(ALL(Table2),Table2[Year]=MAX(Table2[Year])))
return
DIVIDE(_AU,_TU)

Result:

You can download the pbix file from this link: Measure adding monthly values

For more info to Measure and Calculated column: Calculated Columns vs Measures

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.