Forum Discussion

CatW's avatar
CatW
New Member
3 years ago

Grouping/Hierarchy format issue! Help!

Hi Community

 

I wish I could get some advice here to figure out how to get to the report format ( I'm using matrix table in below screenshot) that I need as at the bottom one I normally would do in the excel. Thank you in advance and much appreciate with your help.

 

This is what I can get from Power BI Desktop - Matrix table

with below filters(showing the grouping)

 

Below is the format that I need to achieve

 

Thanks

CatW

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  @,

    I created some data:

     

     

    Here are the steps you can follow：

    1. Create measure.

    Flag =
var _EBITDA=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Account Order.Lv3]="EBITDA"),"Account",[Account])
return
IF(
   NOT(ISINSCOPE('Table'[Account Order.Lv2])),
   SUMX(FILTER(ALL('Table'),'Table'[Account Order.Lv1]=MAX('Table'[Account Order.Lv1])),[FY10]),
  IF(MAX('Table'[Account Order.Lv2]) in{"PBT excl Interest","Interest"}&&NOT(ISINSCOPE('Table'[Account Order.Lv3])),
   SUMX(FILTER(ALL('Table'),'Table'[Account Order.Lv1]=MAX('Table'[Account Order.Lv1])&&'Table'[Account Order.Lv2]=MAX('Table'[Account Order.Lv2])),[FY10])
   ,
  IF(MAX('Table'[Account Order.Lv3]) in {"EBITDA","DMF","FV"}&&NOT(ISINSCOPE('Table'[Account])),
  SUMX(FILTER(ALL('Table'),'Table'[Account Order.Lv1]=MAX('Table'[Account Order.Lv1])&&'Table'[Account Order.Lv2]=MAX('Table'[Account Order.Lv2])&&'Table'[Account Order.Lv3]=MAX('Table'[Account Order.Lv3])),[FY10]),
  IF(MAX('Table'[Account]) in _EBITDA,
SUMX(FILTER(ALL('Table'),'Table'[Account Order.Lv1]=MAX('Table'[Account Order.Lv1])&&'Table'[Account Order.Lv2]=MAX('Table'[Account Order.Lv2])&&'Table'[Account Order.Lv3]=MAX('Table'[Account Order.Lv3])&&'Table'[Account]=MAX('Table'[Account])),[FY10])
  ,BLANK())
   )))

    2. Result:

     

    Best Regards,

    Liu Yang

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

    • CatW's avatar
      CatW
      New Member
      3 years ago

      Hi Liu

      Thank you for your response. Unfortunately it doesn't help as if I look at your result. It shows almost the same result as I got. 

       

      I need to see from the top to show Rev, Cos and OVH which sum up as EBITDA (this row to be formated with lines and bold). Then DMF and FV comes in with another Subtotal as PBT excl Interest (format as per screenshot in my post), then add in interest to come up with another subtotal PBT(format as per screenshot in my post).  Cash and Settlements are additional information I want to show at the bottom.

       

      with Power BI matrix table, as i put in the group/hierarchy in there, it has "PBT, PBT excl Interest and EBITDA" at the very top which I want to remove it. I can manually amend the total at the bottom so it will shows EBITDA, PBT excl interest and PBT.

       

      Hope this clear what I need for you. Thanks.