Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Stacked Column Chart Showing Incorrect Totals

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:
 
CustomerIDDateEvent Count

1

2/6/200
22/6/201
32/6/200
42/6/201
52/6/200
62/6/200
72/6/200
82/6/200
92/6/201

 

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!

Need Help
Show and Tell
Tips and Tricks
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

1 Reply

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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,