Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am trying to create a column in a visual that shows the average margin per specific day (i.e. Monday, Tuesday, etc.) for the last 3 months.
I am calculating the average per day correctly. Here is my measure:
Solved! Go to Solution.
Hi @Kevin_Harper ,
In order to ignore the slicer context you need to add an ALL or ALLSELECTED to your measure in your case when you are considering the WeekdayTable you are not ignoring that context so the result will not be over the last 3 months.
Redo your measure to:
Avg 3 Month REDONE = VAR ThreeMonthDate = CALCULATE ( DATEADD ( LASTDATE ( 'Calendar'[Date] ); -3; MONTH ); ALL ( 'Calendar' ) ) RETURN CALCULATE ( [Average Margin per Day]; FILTER ( 'Calendar'; 'Calendar'[Date] >= ThreeMonthDate ) )
Should give expected result, be aware that I notice some small difference but believe is related with the calculation gets the information for the last date since you are getting different values for each line based on week day.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Kevin_Harper ,
In order to ignore the slicer context you need to add an ALL or ALLSELECTED to your measure in your case when you are considering the WeekdayTable you are not ignoring that context so the result will not be over the last 3 months.
Redo your measure to:
Avg 3 Month REDONE = VAR ThreeMonthDate = CALCULATE ( DATEADD ( LASTDATE ( 'Calendar'[Date] ); -3; MONTH ); ALL ( 'Calendar' ) ) RETURN CALCULATE ( [Average Margin per Day]; FILTER ( 'Calendar'; 'Calendar'[Date] >= ThreeMonthDate ) )
Should give expected result, be aware that I notice some small difference but believe is related with the calculation gets the information for the last date since you are getting different values for each line based on week day.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsYou're awesome, that worked perfectly. Pulling the numbers and doing the averages manually for the time period give them same results as your measure.
Thank you!
Hi @Kevin_Harper ,
Just be carefull when you want to use the ALL or ALLSELECTED because depending on the placement on the measure the results can give you the same result in all rows of the visuals or can give you strange numbers, however this is the formulas to use to ignore all filter contexts on a measure or a calculation.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsYes, I definitely need to study those functions to understand them better. That seems to have made the biggest difference I was missing.
Thanks again!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |