Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.
@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/
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
96 | |
89 | |
73 | |
61 | |
58 |