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.
I want my column chart to be able to show average values based on the period that I filter, eg. if I multi-select JANUARY 2020 & FEBRUARY 2020, the chart will show JANUARY 2020 + FEBRUARY 2020 values/2. Show value as percent of grand total doesn't work for me because I will need the raw values to be displayed on the chart.
Anyone know how I can do this?
Solved! Go to Solution.
Hi, @yong9lum
It’s my pleasure to answer for you.
According to your description,You can create a measure to calculate the desired result.
Like this:
avg =
VAR a =
CALCULATE ( SUM ( 'Table'[value] ), ALLSELECTED ( 'Table' ) )
VAR b =
CALCULATE ( DISTINCTCOUNT ( 'Table'[Period] ), ALLSELECTED ( 'Table' ) )
RETURN
DIVIDE ( a, b )
Here is my sample .pbix file.Hope it helps.
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @yong9lum
It’s my pleasure to answer for you.
According to your description,You can create a measure to calculate the desired result.
Like this:
avg =
VAR a =
CALCULATE ( SUM ( 'Table'[value] ), ALLSELECTED ( 'Table' ) )
VAR b =
CALCULATE ( DISTINCTCOUNT ( 'Table'[Period] ), ALLSELECTED ( 'Table' ) )
RETURN
DIVIDE ( a, b )
Here is my sample .pbix file.Hope it helps.
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@yong9lum , with help from a date table and months to be selected from Slicer , Try a measure like the example
Avg MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])) /CALCULATE(Distinctcount('Date'[Date]),DATESMTD('Date'[Date]), not(isblank(Sales[Sales Amount])))
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.
Hi @yong9lum
This is easy, just create below measures.
Measure 1= sum(value)
Measure 2= Averagex(values(Period),Measure 1
This will work fine, please accept this as solution if this works fine with you.
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 |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |