cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Cumulative total based on sorting order of count total

Hi All,

I have a SQL data source which has multiple columns and many rows, I want to get cumulative total. I have tried several Dax formulas, but nothing is giving me desired result. For my requirement out of many columns i have to use RootCause and MissedCount column and I want to calculate Cumulative total % on descending sort order of MissedCount.

I am able to get cumulative total on sorting order of RootCause using below formula but i want to calculate Cumulative total % based on sorting order of MissedCount.

Cumlative % =
var MissedC=CALCULATE(SUM(Table[MissedCount]),FILTER(ALLSELECTED(Table[RootCause]),ISONORAFTER(Table[RootCause],Max(Table[RootCause]),DESC)))
var totalMissedC=CALCULATE(SUM(Table[MissedCount]),ALLSELECTED(Table))
return DIVIDE(MissedC,totalMissedC)

Expected Cumulative %-

Thanks

1 ACCEPTED SOLUTION
Community Support

Hi @NSC7 ,

1. below is my test table

Table:

2. create measure with below dax formula

``````Measure =
VAR cur_mc =
SELECTEDVALUE ( 'Table'[Missed Count] )
VAR tmp =
FILTER ( ALL ( 'Table' ), [Missed Count] >= cur_mc )
RETURN
SUMX ( tmp, [Total %] )
``````

3. add a table visual with fields and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

Hi @NSC7 ,

1. below is my test table

Table:

2. create measure with below dax formula

``````Measure =
VAR cur_mc =
SELECTEDVALUE ( 'Table'[Missed Count] )
VAR tmp =
FILTER ( ALL ( 'Table' ), [Missed Count] >= cur_mc )
RETURN
SUMX ( tmp, [Total %] )
``````

3. add a table visual with fields and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors