cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

## Top 10 and percentage of total (year filtering)

how to calculate the total for a given year (total in year)and the percentage of the total for a given year (%)?

I have a slicer: 2023/2022/2021. I would like the top 10 of the year to appear after selecting the year.

 nr name date val total in year % 2023_0029 z 09.01.2023 7190000 17191100 42% 2023_0121 x 20.03.2022 6637160 16328882,33 41% 2023_0096 c 17.02.2021 6460938 13105298 49% 2023_0127 v 23.03.2023 3840180 17191100 22% 2023_0179 b 14.04.2022 3672162,33 16328882,33 22% 2023_0040 n 13.01.2021 2584200 13105298 20% 2023_0177 m 05.05.2023 2100760 17191100 12% 2023_0176 a 05.05.2022 2100760 16328882,33 13% 2023_0182 s 13.04.2021 2100760 13105298 16% 2023_0171 d 13.04.2023 2100760 17191100 12% 2023_0178 f 09.05.2022 1959400 16328882,33 12% 2023_0180 g 05.05.2021 1959400 13105298 15% 2023_0174 h 13.04.2023 1959400 17191100 11% 2023_0173 j 13.04.2022 1959400 16328882,33 12%
1 ACCEPTED SOLUTION
Community Support

Hi @Marcin86 ,

``````percentage_of_the_total = DIVIDE(SUM('Table'[val]),[total_in_year])

total_in_year = CALCULATE(SUM('Table'[val]),FILTER(ALL('Table'),YEAR([date])=YEAR(SELECTEDVALUE('Table'[date]))))``````

Then apply the Flag measure to the visual's filter:

``````Flag =
var _a = FILTER(ALL('Table'),YEAR([date])=YEAR(SELECTEDVALUE('Table'[date])))
var _b = RANKX(_a,CALCULATE(SUM('Table'[val])))
return IF(SELECTEDVALUE('Table'[date]) in ALLSELECTED('Table'[date])&&_b<=10,1)``````

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Super User

Hi,

Based on that data that you have shared, show the expected result very clearly.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Hi @Marcin86 ,

``````percentage_of_the_total = DIVIDE(SUM('Table'[val]),[total_in_year])

total_in_year = CALCULATE(SUM('Table'[val]),FILTER(ALL('Table'),YEAR([date])=YEAR(SELECTEDVALUE('Table'[date]))))``````

Then apply the Flag measure to the visual's filter:

``````Flag =
var _a = FILTER(ALL('Table'),YEAR([date])=YEAR(SELECTEDVALUE('Table'[date])))
var _b = RANKX(_a,CALCULATE(SUM('Table'[val])))
return IF(SELECTEDVALUE('Table'[date]) in ALLSELECTED('Table'[date])&&_b<=10,1)``````

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors