Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have tried various solutions but in vain.
I have data distributed like Table 1 below.
What I am looking for is to achieve the the result of Table 2 and Table 3.
That is, to achieve the total of the rows, but only if the value at the selected row dimension (Name) is greater than 1 in both column 2021 and 2022.
This result is also wanted even if I choose a visual without the name dimension
Any help is highly appreciated.
KR Marianne
Solved! Go to Solution.
Hi @MarianneElver ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create measures.
2021' = SUMX(FILTER('Table',[2021]>1&&[2022]>1),[2021])
2022' = SUMX(FILTER('Table',[2021]>1&&[2022]>1),[2022])
% = DIVIDE('Table'[2022']-'Table'[2021'],'Table'[2021'])
(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @MarianneElver ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create measures.
2021' = SUMX(FILTER('Table',[2021]>1&&[2022]>1),[2021])
2022' = SUMX(FILTER('Table',[2021]>1&&[2022]>1),[2022])
% = DIVIDE('Table'[2022']-'Table'[2021'],'Table'[2021'])
(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Neeko,
Thanks for your reply, that solved the sorting out of low values and solved my initial problem!
I've discovered that I also need to af further restriction to the 2021 and 2022 numbers, the % result has to be > -0,5 and <0,5.
I found a way to do this:
I created two new versions of 2021 and 2022:
And similarily for 2022:
and finally a new % metric:
I don't know if there are any more simple way to do this?
Thanks!
KR Marianne
Hi Amitchandak,
Thank you for your help!
I saw your video and I appreciate this solution. However, I have from 200 to 500 rows, and I cannot know specify the "N" in a top N-solution, as the number of rows can vary depending on other filters.
What I am looking for is to be able to specify a minimum row value for the row to be included in totals.
Any suggestion is appreciated.
Thanks,
KR Marianne
@MarianneElver , either you have measures this year vs last year or 2021 and 2022 you need TOPN measures like
Last year top2 = CALCULATE( [Last Year] ,TOPN(2,allselected('Table'[Name]),[Last Year],DESC),values('Table'[Name]))
This year top 2 based on last year =
CALCULATE( [This Year] ,TOPN(2,allselected('Table'[Name]), [Last Year] ,DESC),values('Table'[Name]))
TOPN: https://youtu.be/QIVEFp-QiOk
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
--Sorry, should have left this message as a direct reply:
Hi Amitchandak,
Thank you for your help!
I saw your video and I appreciate this solution. However, I have from 200 to 500 rows, and I cannot know specify the "N" in a top N-solution, as the number of rows can vary depending on other filters.
What I am looking for is to be able to specify a minimum row value for the row to be included in totals.
Any suggestion is appreciated.
Thanks,
KR Marianne
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |