Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Matrix total - turn into %

I have a matrix created where I have a Score column that contains Met and Not Met.  The counts look good but I would like to have the total be a % of the Met.  I'm assuming I need to create a measure?  If so, can anyone post the code for it please?

 

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Anonymous 

    Based on my research, You could try this way as below:

    Step1:

    Create a dim Score table like this:

    Add an additional value "A" in it.

    Step2:

    Create the relationship between them as below:

    Step3:

    Create a measure like this

    Measure 2 = 
IF (
    CALCULATE ( MIN ( 'Dim'[Score] ) ) = "A",
    FORMAT (
        DIVIDE (
            CALCULATE ( COUNTA ( 'Table'[Score] ), 'Table'[Score] = "Met" ),
            CALCULATE ( COUNTA ( 'Table'[Score] ) )
        ),
        "Percent"
    ),
    CALCULATE ( COUNTA ( 'Table'[Score] ) )
)

    Step4:

    Drag Score from dim table and measure into matrix visual

    Result:

    and here is pbix file, please try it.

     

    Best Regards,

    Lin

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    Try clicking on the drop down arrow for the column in your Values area and choosing Show As | Percent of Grand Total.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Greg_Deckler  that changes all columns to a %. I only need the total to be a %

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, Anonymous 

    Based on my research, You could try this way as below:

    Step1:

    Create a dim Score table like this:

    Add an additional value "A" in it.

    Step2:

    Create the relationship between them as below:

    Step3:

    Create a measure like this

    Measure 2 = 
IF (
    CALCULATE ( MIN ( 'Dim'[Score] ) ) = "A",
    FORMAT (
        DIVIDE (
            CALCULATE ( COUNTA ( 'Table'[Score] ), 'Table'[Score] = "Met" ),
            CALCULATE ( COUNTA ( 'Table'[Score] ) )
        ),
        "Percent"
    ),
    CALCULATE ( COUNTA ( 'Table'[Score] ) )
)

    Step4:

    Drag Score from dim table and measure into matrix visual

    Result:

    and here is pbix file, please try it.

     

    Best Regards,

    Lin

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      v-lili6-msft This works but I can't believe there is not a simpler way. Also, this shows every row item including those that don't have data, I'm using a date slicer and items that don't occur in the filtered view are still showing but with no values. When I change the relationship to Both for Cross Filter, it removes the items that are empty but the % goes away