Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
99 | |
86 | |
71 | |
67 |
User | Count |
---|---|
116 | |
109 | |
94 | |
79 | |
72 |