Stacked Column Chart Showing Incorrect Totals

[Anonymous]

I am making a stacked column chart using conditional filtering based on a count of incidents, where if on a given day, there's an incident, the day shows up as red.

I created concatenates of the date and event ID in Power Query to have similar references for each table. They are not themselves related, but they each have the same parent customer table and are both related to my calendar table. I then created a column in my Daily Tracking Table to count how many incidents a day there were for each customer.

Event Count = Var Events =

CALCULATE(

COUNTA(EventTable[DateEventId]),

FILTER(ALL(EventTable),EventTable[DateEventId] = DailyTable[DateEventId]))

RETURN

IF(ISBLANK(Events), 0, Events)

By looking at each value independently in the two tables, it tracks correctly and has the right count for each date. However, when I use the Event Count column in my stacked column chart, the values don't match up.

For a given date, this is what the correct count looks in my Daily Table:

CustomerID	Date	Event Count
1	2/6/20	0
2	2/6/20	1
3	2/6/20	0
4	2/6/20	1
5	2/6/20	0
6	2/6/20	0
7	2/6/20	0
8	2/6/20	0
9	2/6/20	1

The total number of events for this in a given visualization should be 3.

When I put it into the table to verify the total is correct (default to Sum), I instead get this result. 

I have the visualization summing the Event Count, and when I switch it to Max, I get 6 for some reasons.

Guess I'm trying to figure out why the visualization isn't summing the events correctly? Is it something in how I calculated the column in the first place that is causing this to be incorrect? Any help would be much appreciated!

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can use the following measure to meet your requirement.

Event Count =
SUMX (
    VALUES ( CustomerID ),
    VAR Events =
        CALCULATE (
            COUNTA ( EventTable[DateEventId] ),
            FILTER ( ALL ( EventTable ), EventTable[DateEventId] = DailyTable[DateEventId] )
        )
    RETURN
        IF ( ISBLANK ( Events ), 0, Events )
)

We create a sample maybe you can refer.

The measure in our sample like this,

Event Count =
SUMX(
    VALUES(EventTable[CustomerID]),
    Var Events =
    CALCULATE(
        COUNTA(EventTable[CustomerID]))
    RETURN
        IF(EventTable[CustomerID]=9,0,Events)
)

BTW, pbix as attached.

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

Best regards,