cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Spekko
Frequent Visitor

combining x axis hierarchy with running total measures

Hi all,

 

My situation:

I have a dataset with among others:

Dim_Date (date-table)

 

DatekeyYearYearQuarterYearMonthYearWeek
2020010120202020-Q12020-M012020-W01
2020010220202020-Q12020-M012020-W01
...    
2020011020202020-Q12020-M012020-W02
...    

 

Fact_Commit

DatekeyValue
2020010150000
2020011050000
... 

 

These are linked through the Datekey.

 

Then I have various measures. These measures calculate the Running Total of the Commits based on the active filter on the chart.

For instance:

 

Commit RT in YearMonth = CALCULATE( SUM('Fact_Commit'[Commit]),
FILTER( ALLSELECTED('Dim_Date'[YearMonth]),
ISONORAFTER('Dim_Date'[YearMonth],
MAX('Dim_Date'[YearMonth]), DESC) ) )

 

There is a Commit measure for YearQuarter, YearMonth and YearWeek. So 3 separate measures.

I currently have three separate charts, showing the Commit RT and some other data with respectively the YearQuarter, YearMonth or YearWeek field on the X-Axis.

If I add these three fields in one chart, the measure only shows the right result when the date-category on the x-axis is the same as the one used in the measure used in t. 

 

Is it possible to combine the above to one chart, where on the X-Axis I have the hierarchy of YQ, YM and YW. And there is one measure which shows the desired result, depending on the hierarchy-level the user chooses in the X axis?

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@Spekko , If you want to change the axis, you might have to use bookmarks and button

https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

 

refer Dynamically change chart axis in Power BI
https://www.youtube.com/watch?v=6jeSIRpjv0M
https://datamonkeysite.com/2020/10/22/change-dimension-dynamically-using-parameter-in-powerbi/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User IV
Super User IV

@Spekko , If you want to change the axis, you might have to use bookmarks and button

https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

 

refer Dynamically change chart axis in Power BI
https://www.youtube.com/watch?v=6jeSIRpjv0M
https://datamonkeysite.com/2020/10/22/change-dimension-dynamically-using-parameter-in-powerbi/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Ah, right. Completely forgot about the bookmark option, thanks for pointing it out!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors