Forum Discussion

macinrr's avatar
macinrr
Regular Visitor
2 years ago

Cumulative matrix measure

Hi, I have a following model

Issues table with columns: Issue name, Parent name, Initiative name, Released in 

I want to create a matrix visual which will show me following diagram


As you can see, each level calculates its percentage value based on number of issues completed...
What would you suggest to do?

I got to this measure, but it is failing because I cannot skip the blank rows problem... It only increments on value change, does not keep the value from previous column when there is no value change for 2nd level

 


Thanks for help!

 

 

 

 

Cumulative Progress Percentage = 
VAR CurrentRelease = MAX('Releases'[Release name ordered]) // Get the current release based on the matrix column context
VAR CumulativeCount =
    CALCULATE(
        COUNTROWS('Issues'),
        FILTER(
            ALL(Releases[Release name ordered]), // Consider all releases
            'Releases'[Release name ordered] <= CurrentRelease // Filter up to the current release
        ),
        VALUES('Issues'[Initative summary]) // Keep the context of the current Initiative
    )
VAR TotalCount = 
    CALCULATE(
        COUNTROWS('Issues'),
        ALL(Releases[Release name ordered]), // Remove the filter from the release column
        VALUES('Issues'[Initative summary]) // Keep the context of the current Initiative
    )
RETURN DIVIDE(CumulativeCount, TotalCount, 0)

 

 

 

 



Need Help
Show and Tell
Tutorial Requests

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    macinrr , Create a table with the distinct release name and release name order and join it back with your table. Use that in Measure and visual

     

    Cumulative Progress Percentage =
    VAR CurrentRelease = MAX('Releases Name'[Release name ordered]) // Get the current release based on the matrix column context
    VAR CumulativeCount =
    CALCULATE(
    COUNTROWS('Issues'),
    FILTER(
    ALL('Releases Name'), // Consider all releases
    'Releases Name'[Release name ordered] <= CurrentRelease // Filter up to the current release
    ),
    VALUES('Issues'[Initative summary]) // Keep the context of the current Initiative
    )
    VAR TotalCount =
    CALCULATE(
    COUNTROWS('Issues'),
    ALL('Releases Name'), // Remove the filter from the release column
    VALUES('Issues'[Initative summary]) // Keep the context of the current Initiative
    )
    RETURN DIVIDE(CumulativeCount, TotalCount, 0)