Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Community,
I have a table (Table1). I have two slicers "Region" and "Year". I need to create a bar graph which displays the aggregated maximum value of a particular category for the selected year along with the pervious year value of that category based on the "Year" and "Region" selected from slicers. Below is the table and the expected output.
Table1
Id | Attribute | Category | Year | Value | Region |
bf210976 | 1 | A | 2020 | 100 | APAC |
b269211c | 1 | C | 2020 | 30 | EMEA |
b3ae147b | 2 | E | 2020 | 45 | AMER |
8c850f82 | 1 | E | 2020 | 34 | EUR |
e1678c76 | 5 | R | 2020 | 56 | EUR |
517e463b | 1 | B | 2021 | 20 | AMER |
ac5bb55b | 2 | D | 2021 | 10 | APAC |
454c8978 | 4 | Q | 2021 | 70 | EMEA |
69bf32d8 | 5 | Y | 2021 | 78 | APAC |
6abf32d8 | 1 | A | 2021 | 90 | APAC |
27a48ee4 | 2 | B | 2021 | 18 | EMEA |
f46b64c1 | 4 | C | 2021 | 33 | AMER |
ecef8a75 | 5 | R | 2021 | 44 | AMER |
f90970d4 | 7 | U | 2022 | 150 | EMEA |
08c3e5ba | 3 | R | 2022 | 34 | APAC |
92e24e31 | 4 | A | 2022 | 12 | EMEA |
For example: If the year is selected as 2021 then output should be:
Example 2:
if the year 2022 is selected then output should be:
These charts should be dynamic based on the "Year" and "Region" slicer selection. It would be really great if someone can help me hear.
Thanks,
Anthony Joseph
Solved! Go to Solution.
Hi @AnthonyJoseph ,
I tried to simulate the issue, but I do not understand how you get 140 for A in the year 2021. The only row I could find that matches that requirement in your data is:
Can you explain how get to the 140? 🙂 I might have misunderstood your query...
[Edit]
Here one that might come close to what you would like to achieve:
Here the code for the measures:
SumValueSelectedYear = VAR _selectedYear = MAX ( Table[Year] ) RETURN CALCULATE ( SUM ( Table[Value] ), Table[Year] = _selectedYear )
SumValueSelectedYearMinus1 = VAR _selectedYear = MAX ( Table[Year] ) - 1 RETURN CALCULATE ( SUM ( Table[Value] ), REMOVEFILTERS ( Table[Year] ), Table2[Year] = _selectedYear )
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hi @AnthonyJoseph ,
I tried to simulate the issue, but I do not understand how you get 140 for A in the year 2021. The only row I could find that matches that requirement in your data is:
Can you explain how get to the 140? 🙂 I might have misunderstood your query...
[Edit]
Here one that might come close to what you would like to achieve:
Here the code for the measures:
SumValueSelectedYear = VAR _selectedYear = MAX ( Table[Year] ) RETURN CALCULATE ( SUM ( Table[Value] ), Table[Year] = _selectedYear )
SumValueSelectedYearMinus1 = VAR _selectedYear = MAX ( Table[Year] ) - 1 RETURN CALCULATE ( SUM ( Table[Value] ), REMOVEFILTERS ( Table[Year] ), Table2[Year] = _selectedYear )
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Thanks @tackytechtom. You are correct! My bad it should 90 not 140 🙂 Thank you so much for helping me.
Also, just wanted to check if there is any way that we can display the values for the category of the selected year instead of prior year.
For example; in the years 2020, 2021, 2022
When I select 2022 and 2020, I should see the top 3 values by categories for 2022 and their corresponding values for 2020 (instead of prior year). Similarly when I select 2022, 2020 and 2019. Visual should display the top 3 categories in 2022 and their category value for 2020 and 2019. Please can you help me to achieve this.
Really appreciate your help.
Thanks,
Anthony Joseph
User | Count |
---|---|
47 | |
44 | |
19 | |
14 | |
14 |
User | Count |
---|---|
107 | |
56 | |
28 | |
20 | |
16 |