Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ttankka
Frequent Visitor

Create columns that aggregate totals for different filters

I need to calculate totals for a given date range and location.

Here is the data set:

ttankka_0-1605544767430.png

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:

ttankka_1-1605544901936.png

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

ttankka_2-1605544992749.png

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

 

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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.

1.pngYou 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. 

View solution in original post

3 REPLIES 3
v-rzhou-msft
Community Support
Community Support

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.

1.pngYou 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

 

 

Fowmy
Super User
Super User

@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 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.