CODEWITHSUNDEEP
matrixpowerbidaxcumulative-sum
Mark
Mark

Reputation: 11

Running Total for non date column in PowerBI matrix

I am running into an issue with having no values show up for certain cells in a matrix, when the should be filled in with the cumulative count. I know the issue is related to no underlying values directly in the row/column, but it still should show the cumulative count.

For example, in the picture below, column 8 for 5/31/22 should show 6, but it appears blank b/c there is no contract_id with has a date of 5/31/22 with a column 8.

My measure for the values section of the matrix is below.

I place the con_seasoning in the column part and date in the row part of the matrix. I have tried using crossjoin but am running into issues where there are values in the row/column that dont exist.

Any help would be appreciated!

matrix

value measure

Upvotes: 0

Views: 190

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12111

You are going to need a separate table for your "seasoning' columns.
Create a new Calculated Table with (or add one manually via PowerQuery):

Dim Seasoning = SELECTCOLUMNS(GENERATESERIES(0, 17, 1), "Seasoning", [Value])

Then update your measure to:

  var MaxSeasoning = MAX('Dim Seasoning'[Seasoning])

Finally, use 'Dim Seasoning'[Seasoning] for your Matrix column.

Additional, update your measure to something similar to the below to only show data when relevant...

Contract Count Default = 
  var maxSeasoning = MAX('Seasoning Table'[Seasoning])
  var totalCount = CALCULATE( DISTINCTCOUNT('Atoka Net Loss'[Id]), REMOVEFILTERS('Seasoning Table'[Seasoning]))
  var seasoningCount = CALCULATE( DISTINCTCOUNT('Atoka Net Loss'[Id]), REMOVEFILTERS('Seasoning Table'[Seasoning]), 'Atoka Net Loss'[con_seasoning] <= maxSeasoning)
  RETURN IF(totalCount > 0, COALESCE(seasoningCount, 0), seasoningCount)

Upvotes: 0

Related Questions