Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I have a table like this:
id | Date | Category | Location |
1 | 1/1/2020 | Red | US |
2 | 1/1/2020 | Red | UK |
4 | 1/2/2020 | Yellow | Canada |
5 | 1/3/2020 | Blue | Mexico |
6 | 1/3/2020 | Red | Germany |
7 | 1/4/2020 | Green | UK |
Each record is an instance/transaction
I have to calculate the average of the occurence of/number of transactions for each Category. A month may have data for a few days only or may have data for the whole month (like February will have data for only 15 days).
For example, I need to answer the question:
There were on average 20 transactions each day for the Category 'Red' in the Month of January 2020.
How can we do this using a measure? Is it necessary to evaluate count on each day first in a separate table(using summarize function_ or can we do it in 1 measure?
Thanks in advance!!
Solved! Go to Solution.
@Hussain hello Hussain. Yes the suggestion of month year is based on heirarchy.
So that if you have a date heriarchy you can have that in your date slicer in the report, that way when you filter by month your seeing that in a more granular view with year as additional context. So instead of looking at January for all January's in all years you are looking specifically at January for say 2019 for example.
Or by simply providing month year you can view in the slicer as for example jan-2019
Feb-2019..etc
If this answer helped, please mark it as a solution.
@Hussain , Try a new measure Like
Assumed you have month year column
averagex( VALUES( Dates[Date] ), calculate( countrows( 'Table' ) ) )
or
calculate(averagex( VALUES( Dates[Date] ), calculate( countrows( 'Table' ) ) ), allexpcept(Table, Table[Month-Year]))
I am using the formula AVERAGEX(VALUES('Dates'[Date]),CALCULATE(COUNTROWS('Table'))). My question is why doesn't this formula work without calculate function? I mean like this:
AVERAGEX(VALUES('Dates'[Date]),COUNTROWS('Table'))?
Thanks!!
Hi,
The calculation function helps with context transition. It switches from filter to row context. Alternatively, you may try these 2 measures
Measure1 = COUNTROWS('Table')
Measure2 = AVERAGEX(VALUES('Dates'[Date]),[measure1])
Hope this helps.
Thanks Amit and @mpicca13 ,
What do you mean by Month-Year Column? I have date column in the table. I can create a column for Month and Year in which the values are like these:
Jan-2020,Feb-2020(MMM-YYYY format or MM-YYYY format)?
Is this what you are suggesting?
@Hussain hello Hussain. Yes the suggestion of month year is based on heirarchy.
So that if you have a date heriarchy you can have that in your date slicer in the report, that way when you filter by month your seeing that in a more granular view with year as additional context. So instead of looking at January for all January's in all years you are looking specifically at January for say 2019 for example.
Or by simply providing month year you can view in the slicer as for example jan-2019
Feb-2019..etc
If this answer helped, please mark it as a solution.
@Hussain assuming you have a Month filter and Category filter in your page, try this formula,
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
89 | |
82 | |
76 | |
64 |
User | Count |
---|---|
142 | |
111 | |
108 | |
99 | |
95 |