data filter

[razieh1990]

Hello 

I appreciate it if you help me.

I have a dataset including all the sales from 2018. 

I want to create a matrix in Power BI similar to the below layout. 

On the page there is a year selection, each year I choose in the slicer, the data for that year should be displayed by month, and also the target should display the target of the selected year and for the rest of the years, the data should be aggregated and displayed yearly.

and the columns should be sorted ascending. for example, if I choose 2022: the layout should be like the matrix below.

for 2024:

for 2022:

 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[razieh1990]

this is the second part for more clarification. I already sent the data sample in my previous reply.

each year has a unique target. 

I want to create a report in Power BI with one matrix and one slicer. 

the slicer value is years and only one year can be chosen.

the matrix value is " sale." the column value is dynamic however the layout is the same.

Year 1 not selected from the slicer

Year 2 not selected from slicer

Year 3 not selected from slicer

Year 4 not selected from slicer

Year 5 not selected from slicer

Target

January

February

March

April

May

June

July

August

September

October

November

December

Sum of sale

for example, if we choose 2023 from the slicer, the matrix shows the  yearly sales of the years that are not selected but shows the monthly sales  and the target of the year selected as below:

2018

2019

2021

2022

2024

Target of the chosen year from slicer which is 2023

January (2023)

February  (2023)

March  (2023)

April(2023)

May(2023)

June(2023)

July(2023)

August(2023)

September(2023)

October(2023)

November(2023)

December (2023)

Sum of sale

and when I choose 2021:

2018

2019

2022

2023

2024

Target of the chosen year from slicer which is 2021

January (2021

February  (2021)

March  (2021)

April(2021)

May(2021)

June(2021)

July(2021)

August(2021)

September(2021)

October(2021)

November(2021)

December (2021)

Sum of sale

[lbendlin]

You lost me.  Do you want something like this?

[razieh1990]

Hello Ibendlin,

Here is the power bi file and I explained exactly what I want. 

https://we.tl/t-0e0eRtGnjY 

I appreciate your time.

https://we.tl/t-0e0eRtGnjY

[razieh1990]

below is the data sample :

Month	Year	Target	Sale
January	2018	500	100
February	2018	500	2000
March	2018	500	7808
April	2018	500	6800
May	2018	500	13999
June	2018	500	500
July	2018	500	400
August	2018	500	500
September	2018	500	600
October	2018	500	1700
November	2018	500	300
December	2018	500	800
January	2019	600	900
February	2019	600	500
March	2019	600	100
April	2019	600	2000
May	2019	600	890
June	2019	600	600
July	2019	600	600
August	2019	600	500
September	2019	600	400
October	2019	600	500
November	2019	600	600
December	2019	600	1700
January	2022	900	300
February	2022	900	800
March	2022	900	900
April	2022	900	500
May	2022	900	400
June	2022	900	500
July	2022	900	600
August	2022	900	1700
September	2022	900	300
October	2022	900	800
November	2022	900	900
December	2022	900	500
January	2021	700	100
February	2021	700	2000
March	2021	700	890
April	2021	700	600
May	2021	700	600
June	2021	700	500
July	2021	700	400
August	2021	700	500
September	2021	700	600
October	2021	700	1700
November	2021	700	300
December	2021	700	800
January	2023	1000	900
February	2023	1000	500
March	2023	1000	400
April	2023	1000	500
May	2023	1000	600
June	2023	1000	1700
July	2023	1000	300
August	2023	1000	800
September	2023	1000	900
October	2023	1000	500
November	2023	1000	100
December	2023	1000	2000
January	2024	900	890
February	2024	900	600
March	2024	900	600