Matrix table in Power bi

[PowerBI-P]

Hi All,

Please help me with this problem if you have any idea.

I'm trying to work on matrix in Power BI as given below example.

Then I created Average measure to get the values after "Total column" but when I place the avg measure in to the values in Power BI then format is getting like below i.e., Total and Avg is displaying under each day but I don't want like this.

I would like to display like below.

Please advise anyone has idea.

Thanks,

P!

[danextian · Accepted solution]

Hi @PowerBI-P 

Create a display table which is disconnected from the rest of the model, containing the column header values.

Create a measure referencing the table

Display Table Measure = 
VAR _total = [Total Transactions]
VAR _avg =
    AVERAGEX ( ALLSELECTED ( Dates[Day of Week Short] ), [Total Transactions] )
VAR _day =
    CALCULATE (
        [Total Transactions],
        TREATAS ( VALUES ( DisplayTable[Day of Week Short] ), Dates[Day of Week Short] )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( DisplayTable[Day of Week Short] ),
        "Total", _total,
        "Avg", _avg,
        _day
    )

Please see the attached pbix.

[Ashish_Mathur]

Hi,

This can be done with calculation groups.  Share the download link of the PBI file.

[PowerBI-P]

Thank you, @ashish.

[v-hashadapu]

Hi @PowerBI-P , Thank you for reaching out to the Microsoft Community Forum.

We find the answer shared by @Natarajan_M & @MFelix  is appropriate. Can you please confirm if the solution worked for you. It will help others with similar issues find the answer easily.

Thank you @Natarajan_M & @MFelix  for your valuable response.

[Natarajan_M]

Hi @PowerBI-P , If the template is fixed and still you want to get data in the format we can make use of the SVG 

Matrix.pbix
Please find the attached pbix for more details.

Thanks

If this response was helpful in any way, I’d gladly accept a kudo.
Please mark it as the correct solution. It helps other community members find their way faster

[PowerBI-P]

Hi Natarajan,

Sorry for the late reply due to on holidays. 

Thank you for your help. I tried to open Matrix.pbix file but it is not allowing me becuase my IT has blocked the site of outside links. 

Is it possible to send Matrix.pbix file to my personal email if I mention here, please?

Thanks,

P

[MFelix]

Hi @PowerBI-P ,

This is related with the way that matrix work, when you place any value on the matrix this will be applied for all rows and for all columns and you are not able to hide it, you can turn on/off the totals but not the values so that Avg is repeated in all the columns.

For this you can try and use the calculation groups to get the correct values with a single metric in this case.

The best explanation I know is from @parry2k .

[PowerBI-P]

Hi @MFelix,

Sorry for late reply due to on holidays. Thank you so much for your help.

Thanks,

P