Forum Discussion
Visualizations with different filters
Hi weeksd ,
The example I have used is just on some mockup data I have and based on months has I reported, so each month have is own bar chart.
In Power BI you can achieve this by using a measure has I refered previously or using the samll multiples making a filter based on the information.
In Power BI calculations are based on context meaning that filters, columns, others visualizations and so on influence the result of your visualizations. This also means that the way you have your data setup also impacts the calculations.
If you unpivot your data you will only need a single measure for the previous year calculation, however if you keep your model with the A, B and C column you will need to have more than one measure.
For using the small multiples you just need to adjust to show the last 2 years.
I have pickup your data again and made a small file with both options, I also added 2019 data to have the filtering. Also only have to slicers but you can had has many has you need.
Using the previous year measure you need to have the use of 3 measures:
Previous Year A = CALCULATE(SUM(DATA[A]),FILTER(ALL(DATA[Year]), DATA[Year]= MAX(DATA[Year]) -1)) Previous Year B = CALCULATE(SUM(DATA[B]),FILTER(ALL(DATA[Year]), DATA[Year]= MAX(DATA[Year]) -1)) Previous Year C = CALCULATE(SUM(DATA[C]),FILTER(ALL(DATA[Year]), DATA[Year]= MAX(DATA[Year]) -1))Result is below:
Using the small multiples you need to create a table or the years and a measure for the filter:
TABLE Years = DISTINCT(DATA[Year]) YearFilter = if(VALUES(DATA[Year]) <= MAX(Years[Year]) && VALUES(DATA[Year]) >= MAX(Years[Year]) -1 , 1)Now use the year from the new table has you slicer and the Yearfilter on your visualization setting up the value for non blank values:
Check PBIX file attach.
Believe that probably I'm not understanding your request but be aware that the interactions between your visualizations and your measures and calculations is very important in Power BI and this concept makes all the difference between the results.
If I can assist in any other way please tell me.
As it seems that my help request answers are not what I actually require I am closing this as un-resolved. Thanks all
Hi weeksd ,
Have you tried using the small multiples on the visualization to create the multiples charts without making more than one visualization?
https://powerbi.microsoft.com/en-us/blog/announcing-small-multiples-public-preview/
Hi weeksd ,
The example I have used is just on some mockup data I have and based on months has I reported, so each month have is own bar chart.
In Power BI you can achieve this by using a measure has I refered previously or using the samll multiples making a filter based on the information.
In Power BI calculations are based on context meaning that filters, columns, others visualizations and so on influence the result of your visualizations. This also means that the way you have your data setup also impacts the calculations.
If you unpivot your data you will only need a single measure for the previous year calculation, however if you keep your model with the A, B and C column you will need to have more than one measure.
For using the small multiples you just need to adjust to show the last 2 years.
I have pickup your data again and made a small file with both options, I also added 2019 data to have the filtering. Also only have to slicers but you can had has many has you need.
Using the previous year measure you need to have the use of 3 measures:
Previous Year A = CALCULATE(SUM(DATA[A]),FILTER(ALL(DATA[Year]), DATA[Year]= MAX(DATA[Year]) -1)) Previous Year B = CALCULATE(SUM(DATA[B]),FILTER(ALL(DATA[Year]), DATA[Year]= MAX(DATA[Year]) -1)) Previous Year C = CALCULATE(SUM(DATA[C]),FILTER(ALL(DATA[Year]), DATA[Year]= MAX(DATA[Year]) -1))Result is below:
Using the small multiples you need to create a table or the years and a measure for the filter:
TABLE Years = DISTINCT(DATA[Year]) YearFilter = if(VALUES(DATA[Year]) <= MAX(Years[Year]) && VALUES(DATA[Year]) >= MAX(Years[Year]) -1 , 1)Now use the year from the new table has you slicer and the Yearfilter on your visualization setting up the value for non blank values:
Check PBIX file attach.
Believe that probably I'm not understanding your request but be aware that the interactions between your visualizations and your measures and calculations is very important in Power BI and this concept makes all the difference between the results.
If I can assist in any other way please tell me.
But the small multiples makes one single visual with the entire information.
the only diifference is that creates a chart for each of the "small multiples" in your case year.
In the image below I have a small mulitple based on month number but has you can see all charts are the same but they are divided in 4 months:
repl yMFelix ,
You are missing the point here. There HAS to be 2 seperate visualizations with one depicting the current years data and the 2nd showing the previous. The client may not select an entire year to display or they may. The report needs to be exactly as I show in the screen shot I added.
I am starting to thinnk Power BI cannot do this and may have to look at Excel which I know can.
No problem and sorry if I did not get your request at first time.
Don't forget to mark correct answer to help other users