Formatting dashboard with multiple filters

[Anonymous]

Hello all, 
I need your help formatting the dashboard specific way that I'm struggling with. Below are 2 tables that I'm using 

Table 1

Site	Date	Type	Sales
2	10/1/2024	Coke	50
1	09/30/2024	Apple	50
2	9/29/2024	Apple	125
1	9/29/2024	Coke	100
1	9/15/2024	Pepsi	500
2	9/1/2024	Pepsi	75
2	7/5/2024	Coke	75
1	6/30/2024	Apple	150
1	9/27/2024	Pepsi	200
2	9/30/2024	Pepsi	100
2	7/28/2024	Apple	225

Table2:

Site	Apple_goal	Coke_goal	Pepsi_goal
1	2	1	2
2	3	.5	1.5

Table 2 is the daily goals for those items. What I need to do is I need to first calculate goals for 7D, 30D, and 365D and bring them in along with the other columns so they are side by side and then calculate the different. See below the for the end result. Is this possible in PBI? Please help!! Below is what I'm trying to achieve.

Site	Coke	Coke_Goal	Diff	Pepsi	Pepsi_Goal	Diff	Apple	Apple_Goal	Diff
1
7Day	100	7	93	200	14	186	50	14	36
30Day	100	30	70	700	60	640	50	60	-10
365Day	100	365	-265	700	730	-30	200	730	-530
2
7Day	50	3.5	46.5	100	10.5	89.5	125	21	104
30Day	50	15	35	100	90	10	125	90	35
365Day	125	182.5	-57.5	175	547.5	-372.5	350	1095	-745

[Anonymous · Accepted solution]

Hi  @Anonymous ,

You can try the following dax:

Bulk =
VAR _7day =
    TODAY () - 7
VAR _30day =
    TODAY () - 30
VAR _365day =
    TODAY () - 365
RETURN
    SWITCH (
        TRUE (),
        MAX ( 'Table'[Value] ) = "7Day",
            SUMX (
                FILTER (
                    ALL ( 'Table1' ),
                    'Table1'[Site] = MAX ( 'Table'[Site] )
                        && 'Table1'[Date] > _7day
                        && 'Table1'[Type] = "Apple"
                        && 'Table1'[Bulk] <> "False"
                ),
                [Sales]
            ),
        MAX ( 'Table'[Value] ) = "30Day",
            SUMX (
                FILTER (
                    ALL ( 'Table1' ),
                    'Table1'[Site] = MAX ( 'Table'[Site] )
                        && 'Table1'[Date] > _30day
                        && 'Table1'[Type] = "Apple"
                        && 'Table1'[Bulk] <> "False"
                ),
                [Sales]
            ),
        MAX ( 'Table'[Value] ) = "365Day",
            SUMX (
                FILTER (
                    ALL ( 'Table1' ),
                    'Table1'[Site] = MAX ( 'Table'[Site] )
                        && 'Table1'[Date] > _365day
                        && 'Table1'[Type] = "Apple"
                        && 'Table1'[Bulk] <> "False"
                ),
                [Sales]
            )
    )

Bulk_Goal =
VAR _7day =
    TODAY () - 7
VAR _30day =
    TODAY () - 30
VAR _365day =
    TODAY () - 365
VAR _switch =
    SWITCH (
        TRUE (),
        MAX ( 'Table'[Value] ) = "7Day", 7,
        MAX ( 'Table'[Value] ) = "30Day", 30,
        MAX ( 'Table'[Value] ) = "365Day", 365
    )
RETURN
    IF (
        MAX ( 'Table'[Site] ) = 1,
        _switch,
        SUMX (
            FILTER ( ALL ( 'Table2' ), 'Table2'[Site] = MAX ( 'Table'[Site] ) ),
            [Bulk_goal]
        ) * _switch
    )

Bulk_Diff =
[Bulk] - [Bulk_Goal]

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.

[Anonymous]

@Anonymous 

Hello,

First of all, thank you for taking the time to post on my board and assisting me with formatting my dashboard. Your solution worked perfect!! 

There is one additional change I wanted to make and see if you can help me figure this out.

Table 1

Site	Date	Type	Sales	Bulk
2	10/1/2024	Coke	50	False
1	09/30/2024	Apple	50	True
2	9/29/2024	Apple	125	False
1	9/29/2024	Coke	100	False
1	9/15/2024	Pepsi	500	False
2	9/1/2024	Pepsi	75	False
2	7/5/2024	Coke	75	False
1	6/30/2024	Apple	150	False
1	9/27/2024	Pepsi	200	False
2	9/30/2024	Pepsi	100	False
2	7/28/2024	Apple	225	True

Site	Apple_goal	Coke_goal	Pepsi_goal	Bulk_goal
1	2	1	2	.2
2	3	.5	1.5	.15

Here I have addeda column "Bulk" where it is only comparable to the Type "Apple".  I want to make 2 changes to what you sent me. 
1) I need to recalculate the Apple total with excluding where Bulk is "False"
2) I need to add another columns for only the Bulk Sales

Please see below for the final result.

Site	Coke	Coke_Goal	Diff	Bulk	Bulk Goal	Bulk Diff
1
7Day	100	7	93
30Day	100	30	70
365Day	100	365	-265
2
7Day	50	3.5	46.5
30Day	50	15	35
365Day	125	182.5	-57.5

[Anonymous]

Hi  @Anonymous ,

When Site=1,Date=2024.9.30 there is no value in 7Days because it is less than Today()-7, I change the date of the data

You can use the CROSSJOIN() function to create a new table by joining [Site] of Table1 with “7Day”, “30Day”, “365Day”. Join to create a new table

Here are the steps you can follow：

1. Create calculated table.

Table =
var _table1=
{"7Day","30Day","365Day"}
var _table2=
DISTINCT('Table1'[Site])
RETURN
CROSSJOIN(
    _table2,_table1)

2. Create measure.

Coke =
var _7day=TODAY()-7
var _30day=TODAY()-30
var _365day=TODAY()-365
return
SWITCH(
    TRUE(),
    MAX('Table'[Value])="7Day",
    SUMX(
        FILTER(ALL('Table1'),
        'Table1'[Site]=MAX('Table'[Site])&&'Table1'[Date]>_7day&&'Table1'[Type]="Coke"),[Sales]),
     MAX('Table'[Value])="30Day",
    SUMX(
        FILTER(ALL('Table1'),
        'Table1'[Site]=MAX('Table'[Site])&&'Table1'[Date]>_30day&&'Table1'[Type]="Coke"),[Sales]),  
    MAX('Table'[Value])="365Day",
    SUMX(
        FILTER(ALL('Table1'),
        'Table1'[Site]=MAX('Table'[Site])&&'Table1'[Date]>_365day&&'Table1'[Type]="Coke"),[Sales]))

Coke_Goal =
var _switch=
SWITCH(
    TRUE(),
    MAX('Table'[Value])="7Day",7,
     MAX('Table'[Value])="30Day",30,  
    MAX('Table'[Value])="365Day",365)
return
IF(
    MAX('Table'[Site])=1,_switch
,
SUMX(
    FILTER(ALL('Table2'),'Table2'[Site]=MAX('Table'[Site])),[Coke_goal]) * _switch)

Coke_Diff =
 [Coke] - [Coke_Goal]

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