Need help in correcting Inverse Sum calculations

[Anonymous]

Hello All,

I am trying to calculate inverse sum similar to https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/342266#M91.

Power Query for Sample Data.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZI/C8IwEMW/imTucPlXXRMrOgYcSweRDl1a0Pr97V3BoTmawyGPBPLjvbxc2yoDBqBWlQq4UO7T5/XsD3rZNv17HsbHPEwjnbWBRa0H1VVbNEpQz6JnCXraomgYRYE1MGgpsLEOlUNjMbDz2BPLNhJbx6KXfdRZbPi/t+LNmjW97qOeXI8seiu4Av1rhibBTOAMcjUlksJzVzhvaoVFzllXgYYipAIMv9jdFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, Platform = _t, #"Scenarion Name" = _t, #"Source Name" = _t, #"Destination Name" = _t, #"Historical Cases" = _t, #"Scenario Cases" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", Int64.Type}, {"Platform", type text}, {"Scenarion Name", type text}, {"Source Name", type text}, {"Destination Name", type text}, {"Historical Cases", Int64.Type}, {"Scenario Cases", Int64.Type}})
in
    #"Changed Type"

Problem statement is, when i select a scenario what ever the platforms got filtered for the selected scenario based on different source and destintion, it should exclude those platforms and give total sum of historycases of other platforms.

I tried the below DAX

_InverseSum = CALCULATE(
    SUM('Table'[Historical Cases]), 
    FILTER(
        ALL('Table'),
        'Table'[Platform] 
        IN EXCEPT(ALL('Table'[Platform]),
        VALUES('Table'[Platform])) 
        ))

but the problem here is.

I have to show that InverseSum values in table visual by including Platform, Source, Dest, and period as below image.

Now when i dont have Source, Dest, Platform in my table visual and i keep only Period and InverseSum i am getting the right values.

I am seriously struck here.
the total value of historycases for NON A, BP platform is 3155.

it should be same even though i keep other columns like source, dest, platform, scenario, because it should exclude these filtereing criteria and give the total sum.

Please help.

Thanks,

Mohan V.

[amitchandak]

@Anonymous , There is no slicer on platform, are you filtering ?

[Anonymous]

@amitchandak  I am not directly filtering platform values using slicer.

I jus showed in table visual what are the platforms getting filtered based on the period and scenario slicers values..

[Anonymous]

@amitchandak  please help.

[AntrikshSharma]

I am sorry for being rude but I have read your whole post multiple times but I am still unable to figure out what you want to achieve, it will be good if you create instruction inside your power BI file and mention and point out exactly what numbers should appear when and where.

[TomMartens · Accepted solution]

Hey @Anonymous ,

this measure is probably providing what you are looking for:

Measure = 
var _theotherplatforms = 
        EXCEPT(
            CALCULATETABLE(
                VALUES('Query1'[Platform])
                , ALL(Query1[Scenarion Name])
                , ALL(Query1[Platform])
            )
            , CALCULATETABLE(
                VALUES('Query1'[Platform])
                , ALL(Query1[Platform])
            )
        )

return
SUMX(
    ADDCOLUMNS(
        _theotherplatforms
        , "v" , 
            var _p = [Platform]
            return
            CALCULATE(SUM('Query1'[Historical Cases]), ALL(Query1[Scenarion Name]) , 'Query1'[Platform] = _p)
    )
    , [v]
)

At least the table visual looks similar:

Regards,

Tom

P.S.: If this does not tackle your challenge, create a pbix that contains sample data, the filters etc, and explain in great detail why you expect the result. Upload the pbix to onedrive or dropbox and share the link. If you use an xlsx to create the sample data upload the xlsx as well. Personally i will just download sample files from onedrive or dropbox.