Cumulative Totaling with repeated rows (values and fields) and date slicer

[navedkhan]

Hi All - i've a very peculiar problem and this is troubling me a lot. I've done extensive research and googling but nothing helped.

Month	Team Name	Errors
Jan-20	India	34
Jan-20	India	40
Jan-20	India	40
Jan-20	India	0
Jan-20	China	14
Feb-20	Chile	81
Feb-20	China	34
Feb-20	China	14
Mar-20	UK	20
Mar-20	UK	0
Mar-20	UK	20

Now i wanted to have these things work for me in Powerbi;

1) Date Slicer when any date is selected table to correspond accordingly

2) Cumulative Totals to work fine with repeated "Team Names" and "Errors". i.e cumulative total to aggregate correctly

3) Dynamic Index to work specially Date filter is applied.

Expected output table to look like this;

Anyone please help as this will help me build a Pareto Analysis of errors???

@V-pazhen-msft @TomMartens @Ashish_Mathur 

[v-gizhi-msft]

Hi,

Please take following steps:

1)Add an index column to original table.

2)Try these measures:

rank =
RANKX (
    GROUPBY ( ALLSELECTED ( 'Table' ), 'Table'[Month], 'Table'[Team Name] ),
    CALCULATE ( SUM ( 'Table'[Errors] ) ),
    ,
    DESC,
    DENSE
)

Cumulative_Errors = 
CALCULATE (
    SUM ( 'Table'[Errors] ),
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Index] <= MAX ( 'Table'[Index] ) )
)

3)The result shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto

[Ashish_Mathur]

Hi,

Based on the data that you have shared, please share very clearly the expected result of

"Cumulative Totals to work fine with repeated "Team Names" and "Errors". i.e cumulative total to aggregate correctly"

[navedkhan]

@Ashish_Mathur 
it means that Columns if they have repeated values then it should add up;

Feb-20	China	34
Feb-20	China	34
Feb-20	Chile	15
Feb-20	Chile	15
Feb-20	China	0

expected table to look like this when Date filter is applied;

Month	Team	Dynamic Index	Cum_Errors
Feb-20	China	1	34
Feb-20	China	2	68
Feb-20	Chile	3	83
Feb-20	Chile	4	98
Feb-20	China	5	98

[navedkhan]

@v-gizhi-msft 

am getting this error when i applied two measures in Powerbi. May be one need to be a 'Column'??

[v-lid-msft · Accepted solution]

Hi  @navedkhan ,

Does this issue have been resolved?Could you please provide more details about it If it still not be resolved? Please don't contain any Confidential Information or Real data in your reply.

Best regards,

[v-lid-msft]

Hi @navedkhan ,

Please try to create a [Index] Column in Power Query Editor firstly and use [Index] column in Measure "Cumulative_Errors".

By the way, PBIX file as attached.

Best regards,