Visualizations with different filters

[weeksd]

I have a requirement to create a report in which a user will select a year from a slicer.   There needs to be 2 bar charts on the report with one showing the year selected in the slicer.  The other chart needs to show the  previous year's data.

For example a user selects 2020 in the slicer.  Chart 1 would show 2020 data and Chart 2 would show 2019 data. 

Any one know how to accomplish this?

[Anonymous]

Hi @weeksd,

Can you please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[weeksd]

I have tried to reply to both users who have replied to my post. I get and error for "invalid parameters" when doing so.  I will try to reply to both here.

Reponse #1:

Using a calendar table. I am very new to PBI and have no idea what that is.  The graphs cannot be combined into one as there could be 52 weeks of data displayed on the X axis.

Response #2

The data is stored in one table which is outlined like below.  The requirements is one slicer showing the year value.  For example 2021 and 2020. The top graph would display the chosen year (2021) and display all data points.  The bottom graph would show the previous year (2020) data points. The values are already aggregated so there is no SUM function required just need to display them.

Table looks like this:

Year    WEEK#         A   B  C

2021           1               7   5   8

2021          2               2   5    8

2020         1                8   8   8

2020         2                7   5   8

[Anonymous]

Hi @weeksd,

Did MFelix 's suggest help for your scenario? If that is the case, you can consider Kudo or accept his suggestion to help others to find this more quickly.

If not, please provide some more information to help us test on them.

Regards,

Xiaoxin Sheng

[weeksd]

Sorry all but I cannot reply yet to individual posts.  In my data example, I forgot to add in a column.

So the data would be more like.  Columns a, B and C are already totaled whne coming from the database.

Year   Week  Product   A   B   C

2020  1              XYZ         7    8   7

2020  1              DEF         7    8   7

2020  2              XYZ         7    8   7

2020  3              DEF         7    8   7

2020  3              GHI         7    8   7

2020  3              XYZ         7    8   7

2021  1              XYZ         7    8   7

2021  1              DEF         5    6   6

2021  2              XYZ         3    9   9

2021  2              DEF         7    8   7

2021  3              DEF         10  18   17

2021  3              GHI         17    18   17

2021  3              XYZ         7    8   7

Again the top visualization would be a column chart for the 2021 datawhere the weeks are one column set showing the products for that week.  The second visualization would be for 2020 and look the same as the top visual.

[MFelix]

Hi @weeksd ,

The calculation need to be done has I refered in my previous post, the number of columns is not an issue since the comparision is only on the year column.

However I once again enphatize the question about your ABC columns, they should be unpivot if the information is not related to each other however if this information is based on fields that are related for example quantities, sales and cost you must create a measure for each column:

Preivous Year = CALCULATE(SUM(DATA[A]),FILTER(ALL(DATA[Year]), DATA[Year]= MAX(DATA[Year]) -1))

[weeksd]

As it seems that my help request answers are not what I actually require I am closing this as un-resolved.  Thanks all

[MFelix]

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/

[weeksd]

@MFelix

This HAS to be 2 seperate visuals.  The client needs to be able to view an entire year if they need.

[MFelix]

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:

[weeksd]

@MFelix 

Unfortunately your example does not show the previous years data in separate visualizations.

Below is what is required.

[MFelix]

Hi @weeksd ,

Be aware that my example was made with months, and in this case you do not need to have the previous year measure.

You need to have only the values because each chart will present the year value, so no need for adicional measures.

[weeksd]

 repl y@MFelix ,

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.

[MFelix · Accepted solution]

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.

[weeksd]

Hi @MFelix

This is exactly what is required.  Thanks for all your assistance and understanding.

[MFelix]

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

[MFelix]

Hi @weeksd ,

Having the data like this you will have some addtional issues, because if you want to show the values for the all the columns A, B, C you need to create a measure for each one the measures should look like:

Preivous Year = CALCULATE(SUM(DATA[A]),FILTER(ALL(DATA[Year]), DATA[Year]= MAX(DATA[Year]) -1))

However if you allow me there are some things I believe you need to think about in order to improve your model:

• If the columns A, B, C are regarding the same values but for different proiducts or similar you should unpivot your table that way you could have a single measure instead of 3 measure for the previous year
• Instead of using the Year and Week you could had a date column (with end of the week date) and use a calendar table to make the calculations this would simplify all your time calculations and you would be abble to compare with previou period, do rolling average and so on much easier.

If you need any assistance on this please tell me.

[MFelix]

Hi @weeksd .

I'm assuming you are using a calendar table in this case you can add a measure for the previous year that depending on the type of selection can be one of the ones I present below:

Previous year = CALCULATE(SUM(Table[Value]), SAMEPERIODLASTYEAR(CalendarTable[Date]))

Previous year = CALCULATE(SUM(Table[Value]), DATEADD(CalendarTable[Date], -1 , YEAR))

There are several options.

Also not sure if this helps but you can place both values on the same chart no need to create two different charts.