Forum Discussion

PowerBI-P's avatar
PowerBI-P
Frequent Visitor
5 months ago
Solved

Matrix table in Power bi

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!

 

 

 

 

Need Help
  • danextian's avatar
    danextian
    5 months ago

    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.

     

8 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 months ago

    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's avatar
      PowerBI-P
      Frequent Visitor
      5 months ago

      Hi MFelix,

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

      Thanks,

      P

  • Natarajan_M's avatar
    Natarajan_M
    Super User
    5 months ago

    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's avatar
      PowerBI-P
      Frequent Visitor
      5 months ago

      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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 months ago

    Hi,

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

  • danextian's avatar
    danextian
    Super User
    5 months ago

    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.

     

  • v-hashadapu's avatar
    v-hashadapu
    Community Support
    5 months ago

    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.