Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Total sum not showing correctly

Dear BI pros,   I am stuck with the typical probelm of showing the correct grand total from a measure ('New Inventory Value'). Basically I am re-calculating the value on stock per item and l...
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    Please try this expression. It triggers fewer SE queries and should be more performant.

     

    New Total 3 =
    VAR thiscontext =
        SUMMARIZE (
            Fact_Values,
            Fact_Values[Item No],
            Fact_Values[Location Code],
            "cQty1", SUM ( Fact_Values[Quantity] )
        )
    VAR summary =
        CALCULATETABLE (
            SUMMARIZE (
                Fact_Values,
                Fact_Values[Item No],
                Fact_Values[Location Code],
                "cValue", SUM ( Fact_Values[Inventory value] ),
                "cQty2", SUM ( Fact_Values[Quantity] )
            ),
            REMOVEFILTERS ( Fact_Values[Location Code] )
        )
    VAR result =
        SUMX (
            FILTER(thiscontext, [cQty1]<>0),
            VAR thisitem = Fact_Values[Item No]
            RETURN
                [cQty1]
                    * DIVIDE (
                        SUMX ( FILTER ( summary, Fact_Values[Item No] = thisitem ), [cValue] ),
                        SUMX ( FILTER ( summary, Fact_Values[Item No] = thisitem ), [cQty2] )
                    )
        )
    RETURN
        result

     

    Pat

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Anonymous , Create a new measure and try

 

New Inventroy Sum = Sumx(Summarize(Table, Item [Item Description], Table[Location Code], "_sum", [New Inventroy]) , [_sum])

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    amitchandak ,

    thanks for the measure.

    It worked like a charm in the dummy file. Unfortunately the real file is too big (42million rows), it ran out of memory. 

    Unless you have another solution, I guess I will need to find a way to reduce the rows first.

    Let me know if you can think of another solution.

     

    Thanks,

    SmeeK