Total sum not showing correctly

[Smeek]

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

Every item is stored on different locations.

The data only shows the correct quantity per stock location, but the value is not calculated correctly per stock location.

Therefore I have entered a new measure calculating the correct value. I add up the total value per item, divide it with the total quantity of the item to receive the price per item. This I then multiply with the quantity shown on the stock location.

Since I have items with zero quantity but an overall value, this value needs to be set to zero, which the measure correctly does (dividing by zero quantity results in no value). 

Therefore I will end up with a new overall value. It shows up correctly on an item level, but not as the overall total.

Here is the measure I have come up with so far:

I guess I have to somehow add a filter on the item number, but I am not sure where. I tried various ways, but always ended up in a mess. 

Since I do not have a cloud storage from my work that is accesible from outside the company, I have pasted the fact_table below, in case that helps.

I also have a dimension table for the items, from which the item description is pulled, but I guess that is not really relevant

Item No	Location Code	Quantity	Inventory value
IN-1116	LC-11	14	66,57
IN-1116	LC-146	0	2,75
IN-1117	LC-11	0	0,57
IN-1117	LC-146	0	-0,57
IN-1118	LC-7	0	0,03
IN-1118	LC-58	0	-0,03
IN-1119	LC-7	0	50.000,00
IN-1119	LC-58	0	20,00
IN-1120	LC-11	12	38,87
IN-1120	LC-26	0	-22,74
IN-1120	LC-46	0	-0,42
IN-1120	LC-47	0	-2,00
IN-1120	LC-67	0	0,01
IN-1120	LC-146	0	38,60
IN-1121	LC-67	0	-2,44
IN-1121	LC-146	-2	-32,64
IN-1123	LC-67	0	-2,40
IN-1123	LC-146	-2	-26,35
IN-1125	LC-11	19	855,81
IN-1125	LC-47	0	185,50
IN-1125	LC-48	0	-162,31
IN-1126	LC-11	19	169,29
IN-1126	LC-46	0	-0,01
IN-1126	LC-47	0	-30,87
IN-1127	LC-11	22	173,14
IN-1127	LC-47	0	-3,05
IN-1127	LC-55	0	-3,13
IN-1128	LC-11	143	1.255,92
IN-1128	LC-53	14	125,79
IN-1130	LC-11	19	146,16
IN-1130	LC-47	0	49,21
IN-1131	LC-11	27	496,75
IN-1131	LC-47	0	-129,09
IN-1132	LC-11	337	479,80
IN-1132	LC-47	0	39,23
IN-1134	LC-3	0	-0,01
IN-1134	LC-11	0	0,01
IN-1138	LC-11	1	3,33
IN-1138	LC-53	11	91,96
IN-1138	LC-146	0	5,03
IN-1139	LC-11	30	223,92
IN-1139	LC-46	0	3,83

Can anyone help?

Thanks a mill,

SmeeK

[mahoneypat · Accepted solution]

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

[Smeek]

@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!

[amitchandak]

@Smeek , Create a new measure and try

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

[Smeek]

@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