Need help mapping measures to matrix

[maggagnon]

Hi,

I am new to Power Bi and have found this community to be great.  I need your help with a report I have been working WAY too long on. 

I have a 4 level template that I loaded in Powerbi. The objective is to have the levels available (with corresponding $)  with the drill down function in PowerBi. The template looks something like this: 

I am mapping each item of the template to a measure of about the same name.  Here is the DAX so far. 

Financial Value =

if(    

    ISINSCOPE(Template[Level 4]),

SWITCH(

    SELECTEDVALUE(Template[Simple name]),

    "Approved Authorities", FORMAT([Approved Authority], "$#,##0;($#,##0)"),

),

if(    

    ISINSCOPE(Template[Level 3]),

SWITCH(

    SELECTEDVALUE(Template[level 3]),

    "Authorities", FORMAT([Authorities1], "$#,##0;($#,##0)"),

),

   if(    

    ISINSCOPE(Template[Level 2]),

SWITCH(

    SELECTEDVALUE(Template[Level 1]),

    "Funding", FORMAT([Funding], "$#,##0;($#,##0)"

),

   if(    

    ISINSCOPE(Template[Level 1]),

SWITCH(

    SELECTEDVALUE(Template[Level 1]),

    "Project", FORMAT([Project], "$#,##0;($#,##0)"

))))

It's working allright with the subtotals poping up for each level. My issues is that the levels are repeating in the drill down. I undertand that this is due to the way the template was built but i don't know how to resolve if if I want to keep the subtotals active.  Ideally I would only see Project pop up once and funding once as well.  

Project

  Funding

    Authorities

      Approved Authorities

Thanks so much for you help.

[v-xiaotang]

Hi @maggagnon 

Thanks for reaching out to us.

please refer to these tutorials,

How to create and use a Power BI Hierarchy - YouTube

Power BI Organizational Hierarchy or Chart of Accounts with Parent child functions in DAX - YouTube

Parent and Child functions (DAX) - DAX | Microsoft Learn

Best Regards,

Community Support Team _Tang

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

[maggagnon]

Just realized I forgot a screenshot. 

Financial Value is DAX I provided above

Level 1, 2,3,4 are those in the Template above.

Thanks again for your help