Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Need help in correcting Inverse Sum calculations

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 So...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • TomMartens's avatar
    TomMartens
    6 years ago

    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.

     

AntrikshSharma's avatar
AntrikshSharma
Community Champion
6 years ago
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's avatar
TomMartens
Super User
6 years ago

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.