Hierarchal Percentages

[Boycie92]

Hi,

I am hoping someone can help.

I have a matrix table with Year, Term and Building as a hierarchy in the rows section and the following calculation in my values section:

% Visits Region 1 =

Divide(

    [Total Visits Region 1],

       [Region 1 Target] / 1

) + 0

Referenced Calculations:

Total Visits Region 1 = CALCULATE(COUNTROWS('Data'), 'Data' [Region] = "Region 1")

Region 1 Target = CALCULATE(COUNTROWS('Lookup'),'Lookup'[Region] = "Region 1" )

To explain the business logic:

All buildings in a Region must be visited once per Term.

Region 1 has 6 buildings and there have been 5 visits, so one building has not yet been visited.

I want my matrix table at the building level to say 100% if there has been a visit and 0% if there has not been.

However, I also want my total row to display the overall % so 5/6*100 = 83.33% instead of 100%.

How can I achieve this?

To explain the model:

The Data table is connected to the Lookup table using a one-to-many relationship (using the Building fields and set at single)

A Date table (Year, Term and Date) is connected to the Data table using a one-to-many relationship ( using the date fields and is set at both)

Thanks,

Boycie92

[v-yangliu-msft · Accepted solution]

Hi  @Boycie92 ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Measure =
var _1=CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Region]="1"))
var _2=CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Region]="1"&&'Table'[status]="Yes"))
var _value=IF(MAX('Table'[status])="Yes",1,0)
return
IF(ISINSCOPE('Table'[TErm4]),_value,
DIVIDE(_2,_1))

2. Open the matrix, put [Year], [Region], [Term4] into Rows, and put [Measure] into Values.

3. 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.

[amitchandak]

@Boycie92 , You may have to use isinscope and change the calculation https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/