Filter out low values seen at dimension level in totals

[MarianneElver]

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

[v-tangjie-msft · Accepted 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. 

[MarianneElver]

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:

2021'2 =

SUMX(

        FILTER('Table',

        [2021]>1&&[2022]>1&&(([2022]-[2021])/[2021])<0.5&&(([2022]-[2021])/[2021]>-0.5)),

        [2021])

And similarily for 2022:

2022'2 =

SUMX(

        FILTER('Table',

        [2021]>1&&[2022]>1&&(([2022]-[2021])/[2021])<0.5&&(([2022]-[2021])/[2021]>-0.5)),

        [2022])

and finally a new % metric:

% 2 = DIVIDE('Table'[2022'2]-'Table'[2021'2],'Table'[2021'2])

I don't know if there are any more simple way to do this?

Thanks!

KR Marianne

[MarianneElver]

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

[amitchandak]

@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

[MarianneElver]

--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