Forum Discussion

maggagnon's avatar
maggagnon
Frequent Visitor
3 years ago

Need help mapping measures to matrix

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.

 

 

 

2 Replies