Grand total differs from the sum of all rows in Matrix visual - RealTimeData

[bcadmin_powerbi]

Hello super users!

Working with Power BI for a while now, but only just recently started to work with Streaming Datasets (with Historical Data enabled).

I have had some trouble to figure out how to get the most recent values to show in my matrix, but I finally fixed that.

The main issue here is that I can only use measures to manipulate behaviour. Calculated columns, calculated tables, Power Query, nothing of this is available. Even with measures I cannot use a whole lot of functions because of Streaming Data.

Well, that's all fine. If that are limitations for Streaming Datasests, so be it.

Let's explain a little bit on how the data is coming in this Streaming Dataset:

Every delivery has lines, each line has a Bin (KOELCEL RIJ 01 in this case) and a Weight, but these values can change during the day.

Everytime the Weight or Bin changes, the record is sent to the Streaming Dataset.

So i.e. what happens is that if a weight changes 3 times (line 30.000 in this case), I only need the latest value (in this case: 175).

I managed to get that done by using LastNonBlankValue. In the Matrix, it is plotted correctly. 

In the IF statement, I want if HASONEVALUE = false, I get the sum of all values that are plotted in the matrix (so 500+300+175).

But when I use the Sum function on variable RecentWeight, I get a total of 875???? and if I just sum all values in the Weight column, I get 1.325. None of them are correct. Correct is 975.

What am I missing here??

[amitchandak · Accepted solution]

@bcadmin_powerbi , You need to used summarize or values to force row context to grand total

Sumx(Summarize(Table, [Delivery Number], [Primary Key],[Line No], [Bar Code], "_1", [Most recent Weight]), [_1])

Also check once without having fi of hasonevalue. means  Do not use hasonevalue

[amitchandak · Accepted solution]

@bcadmin_powerbi , You need to used summarize or values to force row context to grand total

Sumx(Summarize(Table, [Delivery Number], [Primary Key],[Line No], [Bar Code], "_1", [Most recent Weight]), [_1])

Also check once without having fi of hasonevalue. means  Do not use hasonevalue

[bcadmin_powerbi]

Hi Amitchandak,

Thank you for your quick response! 

After some fiddling around with your suggestion I came up with the following:

Measure 1 - Returns the value of the Weight field of the most recent record

Most Recent Weight =
LASTNONBLANKVALUE( RealTimeData[RecordUpdatedOn], MAX(RealTimeData[Weight]))

Measure 2 - Returns the total weight, within the context of measure 1

Total Weight =
SUMX(SUMMARIZE(RealTimeData,RealTimeData[DeliveryNo], RealTimeData[PrimaryKey], RealTimeData[LineNo], RealTimeData[BinCode],"_1",[Most Recent Weight]),[_1])

Measure 3 - Used to show the correct weight on either Row or Grand Total level

KG =
IF(HASONEVALUE(RealTimeData[PrimaryKey]),RealTimeData[Most Recent Weight],RealTimeData[Total Weight])

Measure 3 is used as value measure in the Matrix and it gives me exactly the result I need.

Thank you so much Amitchandak! 

Now I was just asking myself: "What if I would combine it all in one measure, where measure 1 and 2 are just variables and measure 3 is actually the RETURN statement."

Well, that just doesn't work as I expect. I would expect the same result, but the Grand Total is suddenly 525 (which is the SUM of all 3 lines with linenumber 30.000)

I'll stick to the working solution for now.

EDIT: Plotting Measure 2 in the Matrix also did the trick. No need for having measure 3.

However I have another issue regaring the Bin. If this changes, the weight is plotted on both bins. It should be only plotted on the most recent bin... also see my comment below.

[bcadmin_powerbi]

@amitchandak 

Ok, so I stumbled on another issue.

This happens when a specific Line SHIFTS from one Bin to another.

See Line Number 40.000 in the image below.

1. The initial record that was sent to the Streaming Dataset, was 450 KG in Bin KOELCEL RIJ 01.

2. An employee changed the weight to 400 KG, and this record also was sent to the Streaming Dataset.

So far, so good. The new Weight of 400 KG was correctly shown in the tables and the Matrix.

3. Now, the employee moves the goods to KOELCEL RIJ 02. This is also sent to the Streaming Dataset.

Now what happens is that Power BI thinks it's now on BOTH Bins.

Which IMO does make sense, because we received an additional record instead of the record we had being modified. 

However, my primary key remains the same (DeliveryNo+LineNo). So in ALL cases, I only want to work with the latest record per Primary Key. The previous records I just don't care about, because the most recent record always has all the information I need and this represents the current situation.

Any thoughts about this?

EDIT:

Must have something to do with Measure 1:

Most Recent Weight =LASTNONBLANKVALUE( RealTimeData[RecordUpdatedOn], MAX(RealTimeData[Weight]))

This calculation should always return 0 if it is not the latest record for the PrimaryKey field. And thus only have a value when it actually IS the latest record. I'm having a hard time making the measure so that it does this... 

[bcadmin_powerbi · Accepted solution]

Ok,

So the final working measure:

KG = 
SUMX(
    SUMMARIZE(
        RealTimeData,
        RealTimeData[PrimaryKey], 
        "RecentWeight", LASTNONBLANKVALUE(RealTimeData[RecordUpdatedOn], MAX(RealTimeData[Weight])),
        "RecentBin", LASTNONBLANKVALUE(RealTimeData[RecordUpdatedOn], MAX(RealTimeData[BinCode]))
    ),    
    IF(
        [RecentBin] = CALCULATE(LASTNONBLANKVALUE(RealTimeData[RecordUpdatedOn], MAX(RealTimeData[BinCode])),ALLEXCEPT(RealTimeData,RealTimeData[PrimaryKey])),
        [RecentWeight],
        BLANK()
    )
)

Basically it does the following:

SUMMARIZE:
Creates a grouped table, where the group is the PrimaryKey (So what's left is distinct PrimaryKey values), and add two fields to this new summarized (In memory) table.
The two fields find the last non-blank value of either the Weight or BinCode field, based on the RecordUpdatedOn field and add it as values to the summarizedtable.

IF Statement:

Checks if the value from the grouped table [RecentBin] equals the Latest BinCode per (ALLEXCEPT) PrimaryKey, based on the last non-blank value in RecordUpdatedOn.
If so, then SUMX is evaluated on the [RecentWeight] field from the summarized table, and otherwise SUMX is evaluated on BLANK.

Hopefully the above is helpfull for someone else.

Thanks @amitchandak for your assistance that led me to this solution.