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.

     

amitchandak's avatar
amitchandak
Super User
6 years ago

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    amitchandak  please help.

     

     

    • 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.