Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Smeek
Frequent Visitor

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').

Smeek_0-1661507059294.png

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:

 

Smeek_1-1661503040772.png

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 NoLocation CodeQuantityInventory value
IN-1116LC-111466,57
IN-1116LC-14602,75
IN-1117LC-1100,57
IN-1117LC-1460-0,57
IN-1118LC-700,03
IN-1118LC-580-0,03
IN-1119LC-7050.000,00
IN-1119LC-58020,00
IN-1120LC-111238,87
IN-1120LC-260-22,74
IN-1120LC-460-0,42
IN-1120LC-470-2,00
IN-1120LC-6700,01
IN-1120LC-146038,60
IN-1121LC-670-2,44
IN-1121LC-146-2-32,64
IN-1123LC-670-2,40
IN-1123LC-146-2-26,35
IN-1125LC-1119855,81
IN-1125LC-470185,50
IN-1125LC-480-162,31
IN-1126LC-1119169,29
IN-1126LC-460-0,01
IN-1126LC-470-30,87
IN-1127LC-1122173,14
IN-1127LC-470-3,05
IN-1127LC-550-3,13
IN-1128LC-111431.255,92
IN-1128LC-5314125,79
IN-1130LC-1119146,16
IN-1130LC-47049,21
IN-1131LC-1127496,75
IN-1131LC-470-129,09
IN-1132LC-11337479,80
IN-1132LC-47039,23
IN-1134LC-30-0,01
IN-1134LC-1100,01
IN-1138LC-1113,33
IN-1138LC-531191,96
IN-1138LC-14605,03
IN-1139LC-1130223,92
IN-1139LC-4603,83

 

Can anyone help?

Thanks a mill,

SmeeK

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@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
Super User
Super User

@Smeek , Create a new measure and try

 

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

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors