Matrix component drill down function, percentages are automatically summed

[Gawain]

Hi Team,As shown in the figure above, the percentage is given by (No. of Applications -Exceeded 10 working days)/No. of Applications, but when we put the fold back, it becomes the sum of the 3 types, We want the result to still be calculated using the above expression, what should we do.Hope to hear from you soon. Thanks.

[Anonymous · Accepted solution]

Hi  @Gawain ,

I created some data:

You can use the following measure：

Measure =
(SUM('Table'[No.ofApplications])-SUM('Table'[Exceeded 10 working days])) / SUM('Table'[No.ofApplications])

If this abovemeasure doesn't get the tone effect, you can use a combination of the If() + ISINSCOPE() functions to calculate the corresponding values for each level:

Measure 2 =
var _totalnoofapplications=
SUMX(ALL('Table'),[No.ofApplications])
var _totalexceeded=
SUMX(ALL('Table'),[Exceeded 10 working days])
var _level1noofapplications=
SUMX(FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year])),[No.ofApplications])
var _level1exceeded=
SUMX(FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year])),[Exceeded 10 working days])
var _level2noofapplications=
SUMX(FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year])&&'Table'[Month]=MAX('Table'[Month])),[No.ofApplications])
var _level2exceededs=
SUMX(FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year])&&'Table'[Month]=MAX('Table'[Month])),[Exceeded 10 working days])
var _level3noofapplications=
SUMX(FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year])&&'Table'[Month]=MAX('Table'[Month])&&'Table'[Group]=MAX('Table'[Group])),[No.ofApplications])
var _level3exceeded=
SUMX(FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year])&&'Table'[Month]=MAX('Table'[Month])&&'Table'[Group]=MAX('Table'[Group])),[Exceeded 10 working days])
RETURN
IF(
    ISINSCOPE('Table'[Group]),DIVIDE(_level3noofapplications-_level3exceeded,_level3noofapplications),
    IF(
       ISINSCOPE('Table'[Month]),DIVIDE(_level2noofapplications-_level2exceededs,_level2noofapplications),
       IF(
        ISINSCOPE('Table'[Year]),DIVIDE(_level1noofapplications-_level1exceeded,_level1noofapplications),
        DIVIDE(_totalnoofapplications-_totalexceeded,_totalnoofapplications))))

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