Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

In Matrix table need measure only in Grand total column

In Matrix table i have used Daily Kms coloumn as coloumn for values and Totals kms as measure for values. For Total Kms i have use (Max-Min) to do my caluclation. Now i dont want to see total kms in daily dates instead want to see only in grand total.Quick help would be much appreciated.

 

 

Need Help
Show and Tell
Tips and Tricks

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

    If you want to display the Total kms in grand total, you can refer to the following solution.

    Sample data 

    1.Create a table to put it to the column of the matrix

    2.Create a measure

    MEASURE =
VAR a =
    MINX (
        FILTER ( ALLSELECTED ( 'Table' ), [Name] = MAX ( 'Table'[Name] ) ),
        [Daily Km]
    )
VAR b =
    MAXX (
        FILTER ( ALLSELECTED ( 'Table' ), [Name] = MAX ( 'Table'[Name] ) ),
        [Daily Km]
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Table (2)'[Column1] ) <> "Grand Total",
        CALCULATE (
            SUM ( 'Table'[Daily Km] ),
            FORMAT ( 'Table'[Date], "" ) IN VALUES ( 'Table (2)'[Column1] )
        ),
        b - a
    )

    Then put the following fields to the matrix and colse the grandtotal of column

    Output

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous . Appreciate for your close soloution. I have a got a problem in here where in values have text for certain days(Attachment below). Using sum function is not prossible for having text and also my requriment for the calucation is to Minus max value from min value.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Anonymous 

        You can create a new column in table 

        Column = IF([Daily Km]<>"Not in use",CONVERT([Daily Km],INTEGER))

        Then put the new column to the measure.

        MEASURE =
VAR a =
    MINX (
        FILTER ( ALLSELECTED ( 'Table' ), [Name] = MAX ( 'Table'[Name] ) ),
        [Column]
    )
VAR b =
    MAXX (
        FILTER ( ALLSELECTED ( 'Table' ), [Name] = MAX ( 'Table'[Name] ) ),
        [Column]
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Table (2)'[Column1] ) <> "Grand Total",
        CALCULATE (
            SUM ( 'Table'[Column] ),
            FORMAT ( 'Table'[Date], "" ) IN VALUES ( 'Table (2)'[Column1] )
        ),
        b - a
    )

         

        Best Regards!

        Yolo Zhu

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.