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

     

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
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

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

mahoneypat,

thanks a lot for your answer.

I still had problems with the amount of data, so I have now split the data into two queries. One query I group (anything older than two years) and then join it with the more recent data.

This way your measure does work, although the grouping takes long.

Thanks again to both of you for your help!