Forum Discussion

as1195's avatar
as1195
Helper I
3 years ago
Solved

Matrix total wrong

Hi,
I have a matrix and for which the value changes according to the slicer selection.Below is the measure
Example=

IF(SELECTEDVALUE(table[text])="Number of cells",[Measure1],[Measure2])
when I am trying to use this as values in matrix am getting blank total for measure2

To fix the matrix total I tried using below dax.But still I am getting blank total
Test =
IF(HASONEVALUE(Sales[Value]),[Measure2],SUMX(VALUES(Sales[Value]),[Measure2]))

Below is the measure2 formula
Measure2 =
VAR AllCells = SUMMARIZE('Sales','Sales'[Country],'Sales'[Category])
RETURN COALESCE(
        FORMAT(COALESCE(CALCULATE(
            DIVIDE(
                SUMX(AllCells, IF(NOT ISBLANK([selection]), [Selected Value])),
                [Total Value]
            )
        ), 1), "0%")
    , 0)

Please help!!

Thanks in Advance!!
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi as1195 
    Please try the following 

     

    Measure2 =
COALESCE (
    FORMAT (
        SUMX (
            SUMMARIZE ( Sales, Sales[Value], Sales[Column1], Sales[Column2] ),
            CALCULATE (
                VAR AllCells =
                    SUMMARIZE ( 'Sales', 'Sales'[Country], 'Sales'[Category] )
                RETURN
                    COALESCE (
                        CALCULATE (
                            DIVIDE (
                                SUMX ( AllCells, IF ( NOT ISBLANK ( [selection] ), [Selected Value] ) ),
                                [Total Value]
                            )
                        ),
                        1
                    )
            )
        ),
        "0%"
    ),
    0
)
    Example=
IF ( SELECTEDVALUE ( Sales[text]) = "Number of cells", [Measure1], [Measure2] )

     

3 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    as1195 
    Please share a screenshot of your matrix visual hiding any sensitive data.

  • as1195's avatar
    as1195
    Helper I
    3 years ago

    tamerj1 
    Thank you for the reply!!
    Please find the below matrix visual.
    When %RSV is selected  (Measure2 in above dax)  am getting blank values for total and its in percentage format

     

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      Hi as1195 
      Please try the following 

       

      Measure2 =
COALESCE (
    FORMAT (
        SUMX (
            SUMMARIZE ( Sales, Sales[Value], Sales[Column1], Sales[Column2] ),
            CALCULATE (
                VAR AllCells =
                    SUMMARIZE ( 'Sales', 'Sales'[Country], 'Sales'[Category] )
                RETURN
                    COALESCE (
                        CALCULATE (
                            DIVIDE (
                                SUMX ( AllCells, IF ( NOT ISBLANK ( [selection] ), [Selected Value] ) ),
                                [Total Value]
                            )
                        ),
                        1
                    )
            )
        ),
        "0%"
    ),
    0
)
      Example=
IF ( SELECTEDVALUE ( Sales[text]) = "Number of cells", [Measure1], [Measure2] )