Sum Measure not sum correct after selecting dates in bar chart.

[Applicable88]

Hello,

I guess I have a problem with a filter context. 

I can provide the measure that I'm using, but I think its a more conceptual question. So I have two not connected tables, where I want to except specific values from the shipping table , which are already in the preparation table:

CountOrdersMeasure = 
Var _CurrentDate = 
Max ( ShippingTable[Date] )

Var _LatestPreparationDate = 
Calculate ( 
    Max ( PreparationTable[Date] ),
    PreparationTable[Date] < _CurrentDate 
)

Var _Orders_S =
Calculatetable (
    Values ( ShippingTable[OrderNo] ) , 
    ShippingTable[Status] = "S"   
)

Var _Orders_P =
Calculatetable (
    Values ( PreparationTable[OrderNo] ) , 
    PreparationTable[Status] = "P",
   PreparationTable[Date] = _LatestPreparationDate)
)

Return
Calculate ( 
    Count ( ShippingTable[OrderNo] ),
    Except (
        _Orders_S,
        _Orders_P
    )
) 

 When I pull the shipping date and the measure into a table visual everything is correct and fine. The sum value according to the date is correct. But when I pull the same  into a bar chart and selecting two bars (two dates) of my choice, the sum of those two selected bars is not returning the totals of those two days.

I tried to use keep- and removefilters modifiers in the last calculate part, but its still not giving me the expected value. 

What is the filter context changing here, and how to modify it to give me correct results?

The only solution I got so far is to get these values into every row "fixed" with a calculated column. Now every value in every row is immutable. But I guess a calculated column isn't the optimalo approach, since its taking up physical storage.

Hope someone has a solution to this. 

Thank you very much in advance. 

Best. 

[Anonymous]

Hi @Applicable88 

Are ShippingTable and PreparationTable two unrelated table? I am confused about your calculate logic.

Here is my understanding to your calculate logic.

_CurrentDate is to get current shipping date in visual. Do you want to calculate the latest date in PreparationTable based on each shipping date in visual? _Order_S and _Order_P are both calculated tables. I am confused about _Order_S. You want to return a table with orderno with filter 1. ShippingTable[Status] = S 2. PreparationTable[Date] = _LatestPreparationDate. However there is no relationship between two tables. I think filter2 won't work. Finally count the orderno which is no in _Order_P and should only in _Order_S.

If I have any misunderstanding on your calculate logic, please tell me. Could you share me a sample file with me? And you can show me a screenshot with the result you want. This will make me easier to solve your problem.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Applicable88]

Hi @Anonymous ,

I did make a small sample data, if its better to understand the problem:

https://drive.google.com/drive/folders/1Fq7YwtEsKhix7HEY2IL-8o9UA803-78v?usp=sharing

I do have the right Sales amount for every day in the table visual and bar chart, but the total is not correct:

Neither when filtering with Table1[Date] in bar chart:

I hope thats helps to understand my problem. 

Cheers.

[Anonymous · Accepted solution]

Hi @Applicable88 ,

If you want to get correct total in table visual, you can try this code.

Measure1 = 
VAR _Summarize =
    SUMMARIZE (
        Table1,
        [Date],
        "No",
            VAR _CurrentDate = Table1[Date]
            VAR _LatestPreparationDate =
                CALCULATE ( MAX ( Table2[Date] ), Table2[Date] < _CurrentDate )
            VAR _Orders_S =
                CALCULATETABLE ( VALUES ( Table1[Order] ), Table1[Status] = "S" )
            VAR _Orders_P =
                CALCULATETABLE (
                    VALUES ( Table2[Order] ),
                    Table2[Status] = "P",
                    Table2[Date] = _LatestPreparationDate
                )
            RETURN
                CALCULATE ( SUM ( Table1[Sales] ), EXCEPT ( _Orders_S, _Orders_P ) )
    )
RETURN
    SUMX ( _Summarize, [No] )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Applicable88]

Hello @Anonymous ,

thank you so much. So to get the correct totals I needed an X-aggregated function?

Can you explain to me why I got wrong totals before? Is it because of the filter context ?

[Anonymous]

Hi @Applicable88 ,

I think this should be caused by filter context. For example, if you create a measure by max, you will get max value in total. If you want to sum result in total, there are two workarounds. 1. You can create a new measure based on this measure, like sumx(table,[max measure]). 2. You can update your measure like mine above, try to create a virtual table and then get result.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Applicable88]

Hello @Anonymous ,

thanks for your reply. I try to make an example.

I indeed have two unrelated physical tables. I comparing dates, so they don't need to be in a relationship.

In reality the tables have listing for every workday. So I make an example for the calculation of 10th January:

The shipping table: 

OrderNo.		Date		Status		Sales
1111		10/01/2022		S		1000
1112		10/01/2022		S		1000
1113		10/01/2022		S		1000
1119		10/01/2022		S		1500
1118		10/01/2022		S		1500
1000		10/01/2022		S		3000

The preparation table:

OrderNo.		Date		Status		Sales
1111		09/01/2022		P		1000
1112		09/01/2022		P		1000
1113		09/01/2022		P		1000
1114		09/01/2022		P		8000
1115		09/01/2022		P		10000
1116		09/01/2022		P		1500

Everyday at work there is a table record from last working day. This table consist order which were already prepared for shipping, but never made it on the shipping truck so far. If Order No. 1253 were prepared and didn't got shipped today its on the preparation table. It stays in the preparation table as long as it got finally shipped out. So the orderNo. are not distinct on that table, in this case the orderNo. will get a new entry with an updated preparation date. 

The shipping table is a record of orders which were shipped on the stated shipping dates. 

So to correctly measure the performance of the shipping department, we always need to deduct those orders from the shipping table, which were already on the preparation list on the "last workday." I emphasize last working day, because Friday is the last working day from Monday point of view. 

Specifically for above example: Total Shipped value performance of 10th january is  9000.

But order no. 1111,1112 and 1113 are orders which were on the preparation table the last working day before, hence are not belonging to the performance of 10th January. 

The real performance value of 10th January is 6000.

The measure above is actually already given me the right sales values per shipping date. 

But when I select the shipping dates in a bar chart visual its not filtering the right total value in the table visual:

375105 + 554310 = 929.415

The Totals are not right. 

It displays right values for every shipping date, but when I click on two shipping dates the totals are not right.