Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I need to calculate totals for a given date range and location.
Here is the data set:
Each employee has a score (ES) which is Est hrs / Emp hrs
Need a column - Company ES - for all employees in date range, even if records in the range are NOT visible:
Need a column - location ES - for date and location. In this case the Company ES includes rows from location 20 even though they are not visible
If more that 1 location is visible then provide the location total for each location.
I have tried many options using sumx, etc.
Thank you,
Tim
Solved! Go to Solution.
Hi @ttankka
You can try to build two unrelated slicer tables to achieve your goal.
Date Table:
Date = CALENDAR(DATE(2020,11,01),DATE(2020,11,20))
Location Table:
Location = VALUES('Table'[Location])
Measure:
M.ES =
VAR _MinDate =
MIN ( 'Date'[Date] )
VAR _MAXDate =
MAX ( 'Date'[Date] )
VAR _SELLocation =
ALLSELECTED ( Location[Location] )
RETURN
CALCULATE (
DIVIDE ( SUM ( 'Table'[Est hrs] ), SUM ( 'Table'[Emp hrs] ) ),
FILTER (
'Table',
'Table'[Date] >= _MinDate
&& 'Table'[Date] <= _MAXDate
&& 'Table'[Location] IN _SELLocation
)
)
Company ES =
VAR _MinDate =
MIN ( 'Date'[Date] )
VAR _MAXDate =
MAX ( 'Date'[Date] )
RETURN
CALCULATE (
DIVIDE ( SUM ( 'Table'[Est hrs] ), SUM ( 'Table'[Emp hrs] ) ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] >= _MinDate
&& 'Table'[Date] <= _MAXDate
)
)
Location ES =
VAR _MinDate =
MIN ( 'Date'[Date] )
VAR _MAXDate =
MAX ( 'Date'[Date] )
VAR _SELLocation =
ALLSELECTED ( Location[Location] )
RETURN
CALCULATE (
DIVIDE ( SUM ( 'Table'[Est hrs] ), SUM ( 'Table'[Emp hrs] ) ),
FILTER (
ALL ( 'Table' ),
'Table'[Location] = MAX ( 'Table'[Location] )
&& 'Table'[Date] >= _MinDate
&& 'Table'[Date] <= _MAXDate
&& 'Table'[Location] IN _SELLocation
)
)
Measure =
IF(MAX('Table'[Date]) IN VALUES('Date'[Date]) && MAX('Table'[Location]) IN VALUES(Location[Location]),1,0)
Build a table visual and drag Measure into filter field, then set it show items when value =1.
Result is as below.
You can download the pbix file from this link: Create columns that aggregate totals for different filters
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @ttankka
You can try to build two unrelated slicer tables to achieve your goal.
Date Table:
Date = CALENDAR(DATE(2020,11,01),DATE(2020,11,20))
Location Table:
Location = VALUES('Table'[Location])
Measure:
M.ES =
VAR _MinDate =
MIN ( 'Date'[Date] )
VAR _MAXDate =
MAX ( 'Date'[Date] )
VAR _SELLocation =
ALLSELECTED ( Location[Location] )
RETURN
CALCULATE (
DIVIDE ( SUM ( 'Table'[Est hrs] ), SUM ( 'Table'[Emp hrs] ) ),
FILTER (
'Table',
'Table'[Date] >= _MinDate
&& 'Table'[Date] <= _MAXDate
&& 'Table'[Location] IN _SELLocation
)
)
Company ES =
VAR _MinDate =
MIN ( 'Date'[Date] )
VAR _MAXDate =
MAX ( 'Date'[Date] )
RETURN
CALCULATE (
DIVIDE ( SUM ( 'Table'[Est hrs] ), SUM ( 'Table'[Emp hrs] ) ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] >= _MinDate
&& 'Table'[Date] <= _MAXDate
)
)
Location ES =
VAR _MinDate =
MIN ( 'Date'[Date] )
VAR _MAXDate =
MAX ( 'Date'[Date] )
VAR _SELLocation =
ALLSELECTED ( Location[Location] )
RETURN
CALCULATE (
DIVIDE ( SUM ( 'Table'[Est hrs] ), SUM ( 'Table'[Emp hrs] ) ),
FILTER (
ALL ( 'Table' ),
'Table'[Location] = MAX ( 'Table'[Location] )
&& 'Table'[Date] >= _MinDate
&& 'Table'[Date] <= _MAXDate
&& 'Table'[Location] IN _SELLocation
)
)
Measure =
IF(MAX('Table'[Date]) IN VALUES('Date'[Date]) && MAX('Table'[Location]) IN VALUES(Location[Location]),1,0)
Build a table visual and drag Measure into filter field, then set it show items when value =1.
Result is as below.
You can download the pbix file from this link: Create columns that aggregate totals for different filters
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Rico,
Thank you for your response. This solution worked for me. I was thinking I would need to do something like this, but would not have solved it without your help.
Thank you,
Tim
@ttankka
Please check these two measures:
Company ES =
CALCULATE(
DIVIDE(
SUM(Table4[EST HRS]),
SUM(Table4[EMP HRS])
),
ALLEXCEPT(Table4,Table4[LOCATION])
)
Location ES =
CALCULATE(
DIVIDE(
SUM(Table4[EST HRS]),
SUM(Table4[EMP HRS])
),
ALLSELECTED(Table4)
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |