Forum Discussion

epelton's avatar
epelton
Icon for Helper I rankHelper I
3 years ago

Measures with shared headers

I am attempting to do the exact same thing as this post, but getting strange results.

 

Solved: Shared header for measures in a matrix - Microsoft Power BI Community

 

I would like to display my measures with a shared header like this...

 

 

 

In the first attempt below, I apply the two columns from "Table" to the columns of a matrix as in the above post. This does not however display the two columns. Only the first one. In the second attempt below, I can accomplish displaying two column headers by moving one of the columns to the values field along with "measure" from "Table". This nearly works, but is displaying the word from column 2 instead of the value from the measure, and the headers are not the values from column two in "table" as I would like.

EDIT: Zirect Labor was Direct Labor. I Noticed that the DAX switch was simplay falling through alphabetically due to the MAX function, so I tried to Zirect to check, and that was happening. I'm not entirely comfortable with this DAX, so I'm not sure if thats what it should do or not, but of course, I want to show each case in the switch statment, so there should be 9 total columns.

 

Here is my measure...

 

Measure =
SWITCH (
    TRUE (),
    MAX ( [Column1] ) = "Current"&& MAX ( [Column2] ) = "Gross Revenue", [Gross Rev],
    MAX ( [Column1] ) = "Current"&& MAX ( [Column2] ) = "Net Revenue", [Net Rev],
    MAX ( [Column1] ) = "Current"&& MAX ( [Column2] ) = "Zirect Labor", [Direct Labor],
    MAX ( [Column1] ) = "YTD"&& MAX ( [Column2] ) = "Gross Revenue", [YTD GR],
    MAX ( [Column1] ) = "YTD"&& MAX ( [Column2] ) = "Net Revenue", [YTD NR],
    MAX ( [Column1] ) = "YTD"&& MAX ( [Column2] ) = "Zirect Labor", [YTD DL],
    MAX ( [Column1] ) = "JTD"&& MAX ( [Column2] ) = "Gross Revenue", [JTD GR],
    MAX ( [Column1] ) = "JTD"&& MAX ( [Column2] ) = "Net Revenue", [JTD NR],
    MAX ( [Column1] ) = "JTD"&& MAX ( [Column2] ) = "Zirect Labor", [JTD DL])
 
My first attempt which matches the post above...

 

My second attempt which at least displays a shared header, but incorrect secondary headers, which I want to be the values in column2

This is the data in "Table"


v-yalanwu-msft 

 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi epelton ,

    Please try expand all down one level in the hierarchy.

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

    • epelton's avatar
      epelton
      Icon for Helper I rankHelper I
      3 years ago

      Thanks Gao. This does display the headers correctly, however, the client does not consider this to be a solution, as they drill up and down while using the report and want both headers displayed regardless.

      • bolfri's avatar
        bolfri
        Icon for Solution Sage rankSolution Sage
        3 years ago

         

         

        Measure = 
SWITCH(TRUE(),
    SELECTEDVALUE(GUI_Headers[Header_lvl1])="Total" && SELECTEDVALUE(GUI_Headers[Header_lvl2]) = "Sum header", [Sum],
    SELECTEDVALUE(GUI_Headers[Header_lvl1])="Total" && SELECTEDVALUE(GUI_Headers[Header_lvl2]) = "Avg header", [Avg],
    SELECTEDVALUE(GUI_Headers[Header_lvl1])="Last day" && SELECTEDVALUE(GUI_Headers[Header_lvl2]) = "Sum LD header", [Sum LD],
    SELECTEDVALUE(GUI_Headers[Header_lvl1])="Last day" && SELECTEDVALUE(GUI_Headers[Header_lvl2]) = "Avg LD header", [Avg LD]
)

         

        Works for me. You can still drill down Region > City. 🙂

         

  • bolfri's avatar
    bolfri
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Can you post here a sample data to work with? eg via wetransfer.com

    Also an information (paint/excel) how do you want this to look like would be cool.

    • epelton's avatar
      epelton
      Icon for Helper I rankHelper I
      3 years ago

      I've updated the post with how I want the headers to be displayed. The data is just floats in measures.