As you see in the video that, to do such things we need a custom table, and sort order is the key to achieve this, So it is possible to get weeks and dates as well, but you will need to define your custom mapping table that way, and i think, for such granularity like Dates, it is a complex and not efficient process to implement.
@@PowerBIHelpline I did for nearly 100 columns when no selections made. Works very well also for that purpose and fast to implement with Excel as a tool to create cells before Power Query. Thank you for this!
@@manassahoo8776 I have posted few videos related to such custom columns for column charts. however, i would still publish more videos on such important requirements.
sort order need to be created for unique values. you may need to concatenate with some other column to make it unique. e.g. Year*100 + month this may result like 202405. and this year_month is unique even if you have many years.
Yes , you can keep quarters as the second row header under year. Since it is calculating correctly for each year, it will also be applied for the quarters once you click on the + to expand it.
@@PowerBIHelpline Like I need to create a separate columns for the main table and the date table and the new date table, then join the 1-31 ,Total 1-31 rows?
In my case I need to show total on the left but the columns should be week number and calendar dates nit the month name. Is it possible here?
As you see in the video that, to do such things we need a custom table, and sort order is the key to achieve this, So it is possible to get weeks and dates as well, but you will need to define your custom mapping table that way, and i think, for such granularity like Dates, it is a complex and not efficient process to implement.
@@PowerBIHelpline I did for nearly 100 columns when no selections made. Works very well also for that purpose and fast to implement with Excel as a tool to create cells before Power Query. Thank you for this!
Hi! with your example how can we make the total an average total instead of a sum...thanks.
Replace the "Total" with"Average" in the custom table. and in the Switch measure, replace the formula with Average, when selected value is "Average"
What if instead of fixed month like Jan- Dec we have to show last 13 months or 6 months and on top of that we have to show an average column
@@manassahoo8776 I have posted few videos related to such custom columns for column charts. however, i would still publish more videos on such important requirements.
Hi .i am getting error...it says the sort can't be done as there are multiple values associated.can u pls help
sort order need to be created for unique values. you may need to concatenate with some other column to make it unique.
e.g. Year*100 + month this may result like 202405. and this year_month is unique even if you have many years.
This is dream come true ☺
Wow! That's Great to know. Stay tuned for many such amazing Solutions in Power BI
Ultimate way! Kudos
Thanks for watching.
Hi Power BI Helpline,how to show Total as first column for Year and Quarter wise levels also, if we have the Date hierarchy?
Yes , you can keep quarters as the second row header under year. Since it is calculating correctly for each year, it will also be applied for the quarters once you click on the + to expand it.
Thank you!!
this doesnt work as you creating fixed table ,how should we make it dynamic?
The Important thing is to see, how we create the measures. with that this can show data same way for all the levels of hierarchy.
@@PowerBIHelpline if we have multiple columns and we want subtotal .pls check on it.i found not working for me
Please share this data set we will practice
This is from Adventure works datasets.
What if the columns are in Days not Months?
A similar approach can be used for any granularity. you need to configure the supporting custom table accordingly.
@@PowerBIHelpline Like I need to create a separate columns for the main table and the date table and the new date table, then join the 1-31 ,Total 1-31 rows?
Thank you very much
Glad to know, the video was helpful to you. Stay tuned for more such interesting topics in Power BI