filter

[Anonymous]

I don't understand how these percentages are created from the row numbers. Would you please expand on this?

Thanks.

Best
D

[sturlaws]

I assume that it is this part of the code which is confusing:

SUMX (
  VALUES ( 'Table'[Month] ),
  VAR _mo = CALCULATE ( SELECTEDVALUE ( 'Table'[Month] ) )
  RETURN
  CALCULATE (
    SUM ( 'Table'[value] ),
      FILTER ( ALL ( 'Table' ), 'Table'[Month] = _mo && 'Table'[cohort] = 1 )
    )
  )
)

The SUMX-function is an iterator. It will perform an operation for each row of the table argument it is given. The syntax is SUMX(Table,Expression). In the code above, VALUES(Table[Month]) is the table argument, as the VALUES-function returns a 1 column table. Then there is a variable, _mo which refers to the Table[month] currently iterated. Then expression argument of SUMX is given by summing the table filtered by _mo and 'Table'[cohort]=1.

When this measure is evaluated on a row in a e.g. a table visual, VALUES(Table[Month]) will return a table with 1 column and 1 row, so the iterations i strictly not needed. But for the grand total, VALUES(Table[Month]) will return all the Table[Month]-values present in the visual, and iterate over each month, and then sum the values.


The table

[sturlaws]

Hi @tomislav_mi 

see if this measure does the trick:

Measure =
DIVIDE (
    SUM ( 'Table'[value] ),
    SUMX (
        VALUES ( 'Table'[Month] ),
        VAR _mo =
            CALCULATE ( SELECTEDVALUE ( 'Table'[Month] ) )
        RETURN
            CALCULATE (
                SUM ( 'Table'[value] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Month] = _mo && 'Table'[cohort] = 1 )
            )
    )
)

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.