Thank you so much for the feedback. Most of the time, we try to post such challenging solutions in Power BI. Stay tuned for many such interesting topics. Thanks again.
Hello! This is a really great approach, but I'm having some trouble implementing it. The two year columns are appearing correctly in my matrix. However, the variance column is not. When I use the code as written, the variance column doesn't show up in the matrix. I removed the first IF statement in the code (the one ending in BLANK()), and now the column appears. However, there are no values in it. Do you have any ideas what might cause that? Thank you!
Wow what a way you create dynamic column measure with such ease. Big shout out. Only trouble i am facing is , your speed at which you go with your problem solving. I take time to understand. Could you possibly slow ?
Great video! I spent a long time managing to do this, but with dynamic dates. It all works well now thanks! But i was wondering. How can you apply this with multiple year selections? If you select years multiple now, the variance columns sums them all. It would however be preferable to have the variance show in between each year selected, as its own calculation
Glad you liked it. Nice idea, I posted a video about comparing 2 selected items from the same slicer. but for multiple selection, and to show all the comparison is a nice idea, i would definitely like to post a video on this topic.
Thank you for the feedback, I think here we solved the problem of CY, PY and Variance. Normally it is very is easy to create CY, PY and variance. but you can not change the column header name to show actual current year and actual previous Year Dynamically based on year selections. and We have solved this particular problem in this video. Your idea to show current month and previous month is good, and we would include in our upcoming videos. Thanks again for the suggestion
If you carefully notice the logic used to show the variance column, you will find that, this way we can add any other such columns like percentage of columns. We just need to extend the DAX measure accordingly.
Thank you very much for sharing knowledge. This tutorial has helped me a lot. How could you do exactly the same thing but putting in the visualization calculated measures in the rows and that the columns show the same information as in the tutorial (Year, Year-1 and % Variation). Do you have a tutorial that explains it? Thank you very much for the help
Thank you for the Feedback. And yes that is possible to keep measures as well in Rows (Not Directly though). i would definitely like to post a related content on this topic.
@@PowerBIHelpline Thanks for your answer. Of course, first I created the calculated measures and then with the "enter data" option, a column with the name of each of the calculated measures so that later in the visualization I can add them but it seems that it does not "communicate correctly" with the columns and i get error. Would that be the way to do it? How would you do it? I can't find the way to solve it, greetings and thank you very much!
This is a single measure, which is showing all 3 values Selected year, its previous year and the variance %, variance % is formatted as Text inside our DAX measure. Here you can not directly apply a sort order based on the variance alone. you may need to create a rank column Separately for that.
This is a great video - thank you for posting. Can this be adapted to show only one column based on a slicer selection? E.g. If I have a slicer in which a user can select a country name, can the matrix show the values for the selected country in a column and the Country name displayed as the matrix column heading?
Thank you for the feedback. yes All other slicers will work based on how it filters the data. as example, if you have a country slicer, it will show values for that country itself. I think you are trying to ask something else than i explained, can you please explain a little more about your requirements. you can always send an email to info@pbihelpline.com for more details.
@@PowerBIHelpline - thank you for responding so quickly. I've started to adapt your solution in this video to suit my requirements but I have 2 further questions... Question 1 If I format the variable (e.g. FORMAT(_totalsales, "#,##0"), all the date columns appear. If I leave the variable unformatted, only the dates selected by the slicer show as in your example. Is there a way to format the variable and only return the dates selected by the slicer? Question 2 Is it possible to append further calculated columns in the same way as you have with Variance, (e.g. Variance and then several other additional calculated columns? thank you for all your help.
@@GaryClutterbuck Thank you for sending the details Answer 1- when we use FORMAT function, it is important to see how it handles BLANK , so for all other dates which are not selected, may have some text values for blanks and you may be getting such issues. a detailed look would be required inside your sample PBIX file. Answer 2- Yes it is possible for additional columns same as variance, but the text need to me available inside the Mapping table. ( Variance was there in our custom mapping table)
@@PowerBIHelpline Thank you once again for your reply. I managed to get both questions resolved. I used the SWITCH function to add additional fields l which seems to work perfectly. I'm very grateful and appreciate you taking the time to reply to my queries. Best wishes.
@@karinamatvejeva3164 Thanks for the suggestion, I think it would be very useful for many Power BI users. I will surely include this in upcoming videos.👍
It is possible if I am selecting any month selected month showing sales unit sold and profit and other previous month showing only sales and unit sold (in unit matrix)?
This is a good question. Yes this is possible, and it will also follow the same approach as in this video, where a different Mapping table will be needed, and we may also take help of Field parameter. I may post a Video content on similar topic. Thanks
Hi, I have created a Matrix chart in power bi, which has last 5days date as column, departments as rows and sales data as values. I want a single column to display average of my sales in a single column for each row to check what is the average sales in last 5 days for that particular department. Please make a video on this.. Please give me solution ASAP. It's urgent
Great job. We want more videos on this kind of topics which is not available in internet.
Thank you so much for the feedback. Most of the time, we try to post such challenging solutions in Power BI. Stay tuned for many such interesting topics.
Thanks again.
Thank you!, the sort order for the table was a pain for me, but I got it!
Glad to know, it was helpful
@@PowerBIHelpline @victhorc.f.2223 how do I achieve the sort order?
Hi I need help witht he sort order , please!!!!!!!!
I really needed this trick.Thank you.
Most welcome 😊
This is super cool! Thanks for sharing
Thank you so much for your valuable feedback
super video ..!! Thanks for sharing.
Thank you! Cheers!
Excellent, as usual; thanks for sharing.
Thanks Victor.
Very helpful, thanks for sharing.
Glad it was helpful!
very good workaround, just observed you can't sort the Final measure though
Hello! This is a really great approach, but I'm having some trouble implementing it. The two year columns are appearing correctly in my matrix. However, the variance column is not. When I use the code as written, the variance column doesn't show up in the matrix. I removed the first IF statement in the code (the one ending in BLANK()), and now the column appears. However, there are no values in it. Do you have any ideas what might cause that? Thank you!
if you want to get the PBIX file used in the demo, you can send an email to us, by mentioning the video title as email subject.
Wow what a way you create dynamic column measure with such ease.
Big shout out.
Only trouble i am facing is , your speed at which you go with your problem solving. I take time to understand.
Could you possibly slow ?
Thanks for watching. Your feedback is important for further improvement.
Great video! I spent a long time managing to do this, but with dynamic dates. It all works well now thanks!
But i was wondering. How can you apply this with multiple year selections? If you select years multiple now, the variance columns sums them all. It would however be preferable to have the variance show in between each year selected, as its own calculation
Glad you liked it.
Nice idea, I posted a video about comparing 2 selected items from the same slicer. but for multiple selection, and to show all the comparison is a nice idea, i would definitely like to post a video on this topic.
Great, Thanks for sharing!
Can we have a video on this CurrentMonth, PreviousMonth, and Variance?
Appreciate your help!!
Thank you for the feedback, I think here we solved the problem of CY, PY and Variance.
Normally it is very is easy to create CY, PY and variance. but you can not change the column header name to show actual current year and actual previous Year Dynamically based on year selections. and We have solved this particular problem in this video.
Your idea to show current month and previous month is good, and we would include in our upcoming videos.
Thanks again for the suggestion
Thank you for sharing;
How we can add another column (percentage of column total) in the same matrix please?
If you carefully notice the logic used to show the variance column, you will find that, this way we can add any other such columns like percentage of columns. We just need to extend the DAX measure accordingly.
Thank you very much for sharing knowledge. This tutorial has helped me a lot. How could you do exactly the same thing but putting in the visualization calculated measures in the rows and that the columns show the same information as in the tutorial (Year, Year-1 and % Variation). Do you have a tutorial that explains it? Thank you very much for the help
Thank you for the Feedback. And yes that is possible to keep measures as well in Rows (Not Directly though). i would definitely like to post a related content on this topic.
@@PowerBIHelpline Thanks for your answer. Of course, first I created the calculated measures and then with the "enter data" option, a column with the name of each of the calculated measures so that later in the visualization I can add them but it seems that it does not "communicate correctly" with the columns and i get error. Would that be the way to do it? How would you do it? I can't find the way to solve it, greetings and thank you very much!
Hi,
How do I sort the variance column in desc/asc order? Thanks!
This is a single measure, which is showing all 3 values Selected year, its previous year and the variance %, variance % is formatted as Text inside our DAX measure. Here you can not directly apply a sort order based on the variance alone. you may need to create a rank column Separately for that.
This is a great video - thank you for posting. Can this be adapted to show only one column based on a slicer selection? E.g. If I have a slicer in which a user can select a country name, can the matrix show the values for the selected country in a column and the Country name displayed as the matrix column heading?
Thank you for the feedback. yes All other slicers will work based on how it filters the data. as example, if you have a country slicer, it will show values for that country itself.
I think you are trying to ask something else than i explained, can you please explain a little more about your requirements.
you can always send an email to info@pbihelpline.com for more details.
@@PowerBIHelpline - thank you for responding so quickly. I've started to adapt your solution in this video to suit my requirements but I have 2 further questions...
Question 1
If I format the variable (e.g. FORMAT(_totalsales, "#,##0"), all the date columns appear. If I leave the variable unformatted, only the dates selected by the slicer show as in your example. Is there a way to format the variable and only return the dates selected by the slicer?
Question 2
Is it possible to append further calculated columns in the same way as you have with Variance, (e.g. Variance and then several other additional calculated columns?
thank you for all your help.
@@GaryClutterbuck
Thank you for sending the details
Answer 1-
when we use FORMAT function, it is important to see how it handles BLANK , so for all other dates which are not selected, may have some text values for blanks and you may be getting such issues. a detailed look would be required inside your sample PBIX file.
Answer 2-
Yes it is possible for additional columns same as variance, but the text need to me available inside the Mapping table. ( Variance was there in our custom mapping table)
@@PowerBIHelpline Thank you once again for your reply. I managed to get both questions resolved. I used the SWITCH function to add additional fields l which seems to work perfectly. I'm very grateful and appreciate you taking the time to reply to my queries. Best wishes.
Great. Is it possible with Weeks ?
Yes, it will work same way for weeks as well.
Can You make the video how to compare data week over week, but for example Thursday / Thusrsday or Friday / Friday? Please😊
@@karinamatvejeva3164 Thanks for the suggestion, I think it would be very useful for many Power BI users. I will surely include this in upcoming videos.👍
Can we do it for current month and previous month as well?
It is possible if I am selecting any month selected month showing sales unit sold and profit and other previous month showing only sales and unit sold (in unit matrix)?
This is a good question. Yes this is possible, and it will also follow the same approach as in this video, where a different Mapping table will be needed, and we may also take help of Field parameter.
I may post a Video content on similar topic.
Thanks
@@PowerBIHelpline yes plz I am waiting ❤️
Can we built Year to Date and Previous Year to Date with Dynamic years?
No trick is required to create this, a YTD measure and Same period last year measure will do the job
Where can I find/download the Contoso file??
Contoso Db in different sizes are freely available on internet, you can just search on google and you will find the links.
Hi,
I have created a Matrix chart in power bi, which has last 5days date as column, departments as rows and sales data as values. I want a single column to display average of my sales in a single column for each row to check what is the average sales in last 5 days for that particular department. Please make a video on this..
Please give me solution ASAP. It's urgent
Thanks for posting your query. Please send the PBIX file with sample data to info@pbihelpline.com.