Dynamic Group Data Working with Date Slider

[Bridgewater]

Hi all,

In a previous post (click here) You all helped me combine sales teams with their respective subteams. I now need to take that same approach and have those sales numbers work with a Date slider. I have been trying for a couple weeks now to no avail. Here is the situation:

Current Data: Sales teams and their sub teams are separated for data entry purposes. For display purposes they need to be combined. The result I am looking for is a clustered bar chart that can be filtered by date.

Team      Sub Team Amount   Date

A		965.44	1-Jan
A		747.36	1-Jan
A		306.36	1-Jan
A		1467.60	2-Jan
A		1717.20	2-Jan
B		433.68	1-Jan
B		1555.08	2-Jan
C		1270.32	1-Jan
D	D1	2203.68	1-Jan
D	D1	1341.84	1-Jan
D	D1	2112.72	2-Jan
D	D2	1371.00	1-Jan
D	D2	478.56	1-Jan
D	D2	903.00	2-Jan
D	D2	634.92	2-Jan
D	D2	1415.40	2-Jan

What the data need to look like: @Phil_Seamark helped with this solution previously, however there were no dates attached (I did not ask for dates in the original post). What you will notice is in this table below the Totals for Subteams D1 and D2 make up the aggregate total for Team D, however, they are still displayed and ranked in order with the rest of the teams.

Team       Amount    Date

D	5395.08	1-Jan
D1	3545.52	1-Jan
A	2019.16	1-Jan
D2	1849.56	1-Jan
C	1270.32	1-Jan
B	433.68	1-Jan
D	5066.04	2-Jan
A	3184.8	2-Jan
D2	2953.32	2-Jan
D1	2112.72	2-Jan
B	1555.08	2-Jan

Here is the calculated table that was given to solve this initially without dates. This table sorts everything correctly but because it simply totals the numbers (which is what I originally asked) I cannot filter it by date.

Table 3 = 
VAR SalesSubTeam =  FILTER(Table1,'Table1'[Sales Sub Team] <> BLANK())
RETURN 
 GROUPBY(
                UNION (
				SELECTCOLUMNS(Table1,"Sales Team",[Sales Team] , "Sales Amount" , [Sale Amount]) ,
				SELECTCOLUMNS(SalesSubTeam,"Sales Team",[Sales Sub Team] , "Sales Amount" , [Sale Amount])
				),
                 [Sales Team],
                 "Sales Amount",SUMX(CURRENTGROUP(),[Sales Amount])
                 )

Any help with this would be useful. 

[Zubair_Muhammad · Accepted solution]

Hi @Bridgewater

Try this calculated Table.

Then add a Date Slider using Date Column from this Calculated Table

New Table =
VAR TeamTable =
    SUMMARIZE (
        TableName,
        TableName[Team],
        TableName[Date],
        "Amount", SUM ( TableName[Amount] )
    )
VAR SubTeamTable =
    FILTER (
        SUMMARIZE (
            TableName,
            TableName[Sub Team],
            TableName[Date],
            "Amount", SUM ( TableName[Amount] )
        ),
        TableName[Sub Team] <> BLANK ()
    )
RETURN
    UNION ( TeamTable, SubTeamTable )

[Zubair_Muhammad]

@Bridgewater

[Zubair_Muhammad]

@Bridgewater

See the file attached here as well

(Using your sample data)

[Bridgewater]

Thank you @Zubair_Muhammad, the sample data worked out perfectly.

There is still as issue whenever I plug in the real data. I am getting this error: 

'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.'

I thought I had plugged in everything exactly as you did with your calculated table. Do you have any insight as to why this error is occurring?

[Bridgewater]

Fixed! Just had to toy around with it.

Thank you @Zubair_Muhammad