In Matrix table need measure only in Grand total column

[Akhil_raj]

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.

[v-xinruzhu-msft]

Hi @Akhil_raj 

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.

[Akhil_raj]

Hi @v-xinruzhu-msft . 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.

[v-xinruzhu-msft]

Hi @Akhil_raj 

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.

[Akhil_raj]

Hi @v-xinruzhu-msft is there a way to display "Not in use" even after doing the caluclation ? dont want to leave the fields blank. Thanks for you help

[v-xinruzhu-msft]

Hi @Akhil_raj 

Try the following measure.

Measure = var a=MINX(FILTER(ALLSELECTED('Table'),[Name]=MAX('Table'[Name])),[Column])
var b=MAXX(FILTER(ALLSELECTED('Table'),[Name]=MAX('Table'[Name])),[Column])
var c=MAXX(FILTER('Table',FORMAT([Date],"") in VALUES('Table (2)'[Column1])),[Column])
return IF(SELECTEDVALUE('Table (2)'[Column1])<>"Grand Total",IF(NOT(ISBLANK(c)),FORMAT(CALCULATE(SUM('Table'[Column]),FORMAT('Table'[Date],"") in VALUES('Table (2)'[Column1])),""),CALCULATE(MAX('Table'[Daily Km]),FORMAT('Table'[Date],"") in VALUES('Table (2)'[Column1]))),FORMAT(b-a,""))

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.

[Akhil_raj]

Hi @v-xinruzhu-msft , tried everything i could with modifications, but still does the sum .please see below. 

Awaiting reply.Appreiciate 

[v-xinruzhu-msft]

Hi @Akhil_raj 

You need to create a new table that it contains the 'Grand Total' value. it means then the value on the matrix column is 'Grand Total' it need to  minus max value from min value.The picture you offered above doesn't contain the 'Grand Total' value.

Please see the attachment I have offered above

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.

[Akhil_raj]

Hi @v-xinruzhu-msft 
Doesnt like to work for me . I have created exact table like you have done.Are you able to connect me on Zoom or teams please?

[v-xinruzhu-msft]

Hi @Akhil_raj 

Please make sure there is no relationship between your data table and fact table, it it is still not work, Can you provide some sample picture, we do not support the contract.

Best Regards!

Yolo Zhu