DAX - filter the table first on slicer and then exclude slicer while calculating sum

[hsing121]

I have below table and a multi select slicer on boot duration . i want a selectedvalue column that gives total of O Success for each vehicle id but it should only total over the selected boot_minutes_duration in the slicer. 

II used the below calculation but that gives overall sum for each vehicle id does not matter what boot duration i select in the slicer

selectedvalue = CALCULATE (

    SUM ( Projection_Boot_Status_By_Vehicle_Id[optimistic_success] ),

        ALLEXCEPT (

            Projection_Boot_Status_By_Vehicle_Id,

            Projection_Boot_Status_By_Vehicle_Id[vehicle_id],

            Projection_Boot_Status_By_Vehicle_Id[branch_version],

            Projection_Boot_Status_By_Vehicle_Id[sub_branch_version]

        )

)

vehicle_id	O Success	O Fail	SB%	boot_minutes_duration	selectedvalue	BD%	P Success	P Fail	SB%	BD%	SubBranch
0B954435BB8BB1EDBDCAA067F7E4773380A96EBF0CE7D1B9D6C33214C5B06727	12	1	95%	>7 Min	21	92%	11	2	91%	85%	2.3.3
0B954435BB8BB1EDBDCAA067F7E4773380A96EBF0CE7D1B9D6C33214C5B06727	2	0	95%	5-7 Min	21	100%	2	0	91%	100%	2.3.3
0B954435BB8BB1EDBDCAA067F7E4773380A96EBF0CE7D1B9D6C33214C5B06727	4	0	95%	3-5 Min	21	100%	4	0	91%	100%	2.3.3
0B954435BB8BB1EDBDCAA067F7E4773380A96EBF0CE7D1B9D6C33214C5B06727	3	0	95%	<= 3 Min	21	100%	3	0	91%	100%	2.3.3
133C188488679EDA33700DD3BCB78A2591D9B304A8077A0F7AD38C61DC2DA601	24	1	94%	>7 Min	33	96%	23	2	91%	92%	2.3.3
133C188488679EDA33700DD3BCB78A2591D9B304A8077A0F7AD38C61DC2DA601	3	1	94%	5-7 Min	33	75%	3	1	91%	75%	2.3.3
133C188488679EDA33700DD3BCB78A2591D9B304A8077A0F7AD38C61DC2DA601	4	0	94%	3-5 Min	33	100%	4	0	91%	100%	2.3.3
133C188488679EDA33700DD3BCB78A2591D9B304A8077A0F7AD38C61DC2DA601	2	0	94%	<= 3 Min	33	100%	2	0	91%	100%	2.3.3

[hsing121]

Thanks @v-jianpeng-msft  its working. i have accepted that as a solution. However, i need to plot it too and use it in x axis, but it being a measure , i m not able to drag it to x axis of a clustered bar chart . any solution for that

[hsing121]

@parry2k i tried that but its not working. i will try to share pbi file or data in sometime

[v-jianpeng-msft]

Hi, @hsing121 

Have you tried the solution I provided? As you described, when slicer selection >=7min and 5-7min, 52,59 is displayed:

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@hsing121 try this measure 

Sum = 
CALCULATE (
    CALCULATE ( 
        SUM ( vehicle[O Success] ),
        ALLEXCEPT ( 
            vehicle, 
            vehicle[vehicle_id], 
            vehicle[SubBranch], 
            vehicle[boot_minutes_duration] 
        )
    ), 
    ALLSELECTED ( vehicle[boot_minutes_duration] )
)

[parry2k]

@hsing121 I don't think you made your file available to download publically, it is asking me to login, can you try again?

[hsing121]

Hi @parry2k , i am not able to share but let me try explaining more. The below data is at vehicle id, sub branch and boot minutes level. Note the sum column, this is summing up the o success column values at vehicle id and sub branch level , the dax for that is 

CALCULATE(SUM(data_for_pbi[O Success]),ALLEXCEPT(data_for_pbi,data_for_pbi[vehicle_id],data_for_pbi[SubBranch]) )

vehicle_id	O Success	O Fail	SB%	boot_minutes_duration	SubBranch	sum
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	51	0	98%	>7 Min	2.0.10	60
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	1	0	98%	5-7 Min	2.0.10	60
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	1	0	98%	3-5 Min	2.0.10	60
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	7	1	98%	<= 3 Min	2.0.10	60
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	58	0	100%	>7 Min	2.0.4	68
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	1	0	100%	5-7 Min	2.0.4	68
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	2	0	100%	3-5 Min	2.0.4	68
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	7	0	100%	<= 3 Min	2.0.4	68

Now , assume i have a boot minutes multi select slicer. i want the sum column to show below values when ">7 min" and "5-7 min" are selected in the slicer. that means , the data should first filter by boot minutes group column and then the sum should be taken at vehicle id and sub branch level 

vehicle_id	O Success	O Fail	SB%	boot_minutes_duration	SubBranch	sum
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	51	0	98%	>7 Min	2.0.10	52
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	1	0	98%	5-7 Min	2.0.10	52
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	58	0	####	>7 Min	2.0.4	59
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	1	0	####	5-7 Min	2.0.4	59

[parry2k]

@hsing121 your requirement is still not very clear, can you post the data in the excel sheet with the expected output with different scenarios and share that excel sheet using one drive/google drive.

[hsing121]

Hi @parry2k , can you try checking this data_for_pbi.csv
basically what i want is that sum of o success column should sum at vehile id and sub branch level but we have a boot minutes group too. each sub branch may have multiple boot minutes group. while summing up the o success column values at sub branch, i want to consider  the slicer selection in boot minutes group but at the same time , i dont want the data to show at boot minutes group level. 

e.g. consider below case and look at sum column

vehicle_id	O Success	O Fail	SB%	boot_minutes_duration	SubBranch	sum
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	51	0	98%	>7 Min	2.0.10	60
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	1	0	98%	5-7 Min	2.0.10	60
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	1	0	98%	3-5 Min	2.0.10	60
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	7	1	98%	<= 3 Min	2.0.10	60
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	58	0	100%	>7 Min	2.0.4	68
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	1	0	100%	5-7 Min	2.0.4	68
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	2	0	100%	3-5 Min	2.0.4	68
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	7	0	100%	<= 3 Min	2.0.4	68

here, if the total sum of o success at sub branch 2.0.10 is 51+1+1+7 = 60 , so when there is no boot minutes selected in boot minutes slicer , the sum column (which i m trying to  derive) should show 60 , but lets say if i select ">7 min" and "5-7 min" , then the table should show only these 2 groups and sum should up as 52 like below

vehicle_id	O Success	O Fail	SB%	boot_minutes_duration	SubBranch	sum
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	51	0	98%	>7 Min	2.0.10	52
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	1	0	98%	5-7 Min	2.0.10	52

[v-jianpeng-msft]

Thank you parry2k 

Hi, @hsing121 

You must create a measure with the following values:

Measure = CALCULATE(SUM('Table'[O Success]),ALLEXCEPT('Table','Table'[boot_minutes_duration],'Table'[vehicle_id]))

This will keep only vehicle_id, boot_minutes_duration as outer filters. Here are the results

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hsing121]

I am attaching the same pbi . the same vehicle can exist at multiple subbranches. the boot minutes duration is a level below subbranch. so vehicle id is highest level group, then subbranch and then bootminutes. what i want is , the sum of o success should always be at vehicle and sub branch level. but if a user is selecting a group of boot minutes e.g. >7 min and 5-7 min then the sub branch should be sum of o success for those boots but summed up at sub branch level. 

[hsing121]

actually i m not able to attach the pbix file, i dont see option anywhere. attaching screenshot below .
with no slicer selected : 

with slicer selected: 

here i the formula for sum_for_subbranch is 

Sum_for_subbranch = CALCULATE(SUM(data_for_pbi[O Success]),ALLEXCEPT(data_for_pbi,data_for_pbi[vehicle_id],data_for_pbi[SubBranch]) )
but what i want the value to show up as , is 8 for all rows of sub branch 2.0.10 and 9 for all rows of sub branch 2.0.4

below is all data 

vehicle_id	O Success	O Fail	SB%	boot_minutes_duration	selectedvalue	BD%	P Success	P Fail	SB%	BD%	SubBranch
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	51	0	98%	>7 Min	60	100%	50	1	97%	98%	2.0.10
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	1	0	98%	5-7 Min	60	100%	1	0	97%	100%	2.0.10
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	1	0	98%	3-5 Min	60	100%	1	0	97%	100%	2.0.10
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	7	1	98%	<= 3 Min	60	88%	7	1	97%	88%	2.0.10
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	58	0	100%	>7 Min	68	100%	57	1	99%	98%	2.0.4
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	1	0	100%	5-7 Min	68	100%	1	0	99%	100%	2.0.4
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	2	0	100%	3-5 Min	68	100%	2	0	99%	100%	2.0.4
000538F1391CEFC8438051087B71DE12B90B7AB02C9C2111A7201C5726861599	7	0	100%	<= 3 Min	68	100%	7	0	99%	100%	2.0.4

[v-jianpeng-msft · Accepted solution]

Hi, @hsing121 

First, use the following expression to create a calculated table:

Table2 = VALUES('Table'[boot_minutes_duration])

Create the following measure:

MEASURE =
CALCULATE (
    SUM ( 'Table'[O Success] ),
    ALLEXCEPT ( 'Table', 'Table'[vehicle_id], 'Table'[SubBranch] ),
    FILTER (
        ALL ( 'Table'[boot_minutes_duration] ),
        'Table'[boot_minutes_duration] IN VALUES ( Table2[boot_minutes_duration] )
    )
)

ShowItem =
VAR _1 =
    IF ( ISFILTERED ( Table2[boot_minutes_duration] ), 1, 0 )
RETURN
    IF (
        _1 = 0,
        1,
        IF (
            SELECTEDVALUE ( 'Table'[boot_minutes_duration] )
                IN VALUES ( Table2[boot_minutes_duration] ),
            1,
            0
        )
    )

Next, set up your visuals:

Here are the results:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@hsing121 why it is not a simple sum, why are you using ALLEXCEPT?