Count showing wrong compare to bar chart

[BalajiBuddha]

Hi Everyone,

I want to sum upp all bar chart visuals and give the total but here it is giving last month total in table thats not correct pelase correct the measure and give me

Quantity by Owner and Snapshot Date =

VAR AutomationSelected =

    CONTAINS (

        VALUES ( OPEN_OPPORTUNITY_EVOLUTION_C4C[Opportunity Type] ),

        OPEN_OPPORTUNITY_EVOLUTION_C4C[Opportunity Type],

        "Automation"

    )

/*  Month context coming from Calendar axis */

VAR MonthStart =

    DATE (

        YEAR ( MAX ( 'CALENDAR'[Date] ) ),

        MONTH ( MAX ( 'CALENDAR'[Date] ) ),

        1

    )

VAR MonthEnd =

    EOMONTH ( MonthStart, 0 )

/*  Latest snapshot WITHIN the month on the axis */

VAR SnapshotDateSelected =

    CALCULATE (

        MAX ( OPEN_OPPORTUNITY_EVOLUTION_C4C[Snapshot Date] ),

        OPEN_OPPORTUNITY_EVOLUTION_C4C[Snapshot Date] >= MonthStart,

        OPEN_OPPORTUNITY_EVOLUTION_C4C[Snapshot Date] <= MonthEnd

    )

/*  Potential Close Date window: snapshot month → +11 months */

VAR CloseDateEnd =

    EOMONTH ( MonthStart, 11 )

RETURN

IF (

    ISBLANK ( SnapshotDateSelected ),

    BLANK (),

    CALCULATE (

        /*  Same aggregation logic that gives correct totals */

        SUMX (

            SUMMARIZE (

                OPEN_OPPORTUNITY_EVOLUTION_C4C,

                OPEN_OPPORTUNITY_EVOLUTION_C4C[Opportunity Key],

                OPEN_OPPORTUNITY_EVOLUTION_C4C[Sales Cycle Phase],

                "Qty_Clean",

                    SUM ( OPEN_OPPORTUNITY_EVOLUTION_C4C[Quantity] )

            ),

            [Qty_Clean]

        ),

        /*  Lock to snapshot of the CURRENT month */

        OPEN_OPPORTUNITY_EVOLUTION_C4C[Snapshot Date]

            = SnapshotDateSelected,

        /*  Potential Close Date range */

        OPEN_OPPORTUNITY_EVOLUTION_C4C[Potential Close Date] >= MonthStart,

        OPEN_OPPORTUNITY_EVOLUTION_C4C[Potential Close Date] <= CloseDateEnd,

        /*  Conditional Automation logic */

        FILTER (

            'PRODUCT_STRUCTURE_C4C',

            NOT AutomationSelected

                ||

                NOT (

                    'PRODUCT_STRUCTURE_C4C'[Model] IN {

                        "Automation Analysis",

                        "Project",

                        "Peripherals",

                        "Change Request",

                        "LSI",

                        "Software",

                        "System Support"

                    }

                )

        )

    )

)

[v-nmadadi-msft]

Hi @BalajiBuddha 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Please show the expected outcome based on the sample data you provided.

Regards

[mussaenda]

Hi @BalajiBuddha ,

Are you using the same measure for both visuals?

[BalajiBuddha]

Yes iam using same measures in both

[pankajnamekar25 · Accepted solution]

Hello @BalajiBuddha 

try this 

Quantity by Owner and Snapshot Date =
VAR AutomationSelected =
CONTAINS (
VALUES ( OPEN_OPPORTUNITY_EVOLUTION_C4C[Opportunity Type] ),
OPEN_OPPORTUNITY_EVOLUTION_C4C[Opportunity Type],
"Automation"
)

/* Detect if we are in a single-month row or in a total/subtotal row */
VAR IsMonthLevel =
HASONEVALUE ( 'CALENDAR'[Month] ) -- swap to [YearMonth] or whichever
-- column drives your axis

RETURN
IF (
IsMonthLevel,

/* ── SINGLE MONTH ROW ── same logic as before */
VAR MonthStart =
DATE ( YEAR ( MAX ( 'CALENDAR'[Date] ) ), MONTH ( MAX ( 'CALENDAR'[Date] ) ), 1 )
VAR MonthEnd = EOMONTH ( MonthStart, 0 )
VAR SnapshotDateSelected =
CALCULATE (
MAX ( OPEN_OPPORTUNITY_EVOLUTION_C4C[Snapshot Date] ),
OPEN_OPPORTUNITY_EVOLUTION_C4C[Snapshot Date] >= MonthStart,
OPEN_OPPORTUNITY_EVOLUTION_C4C[Snapshot Date] <= MonthEnd
)
VAR CloseDateEnd = EOMONTH ( MonthStart, 11 )
RETURN
IF (
ISBLANK ( SnapshotDateSelected ),
BLANK (),
CALCULATE (
SUMX (
SUMMARIZE (
OPEN_OPPORTUNITY_EVOLUTION_C4C,
OPEN_OPPORTUNITY_EVOLUTION_C4C[Opportunity Key],
OPEN_OPPORTUNITY_EVOLUTION_C4C[Sales Cycle Phase],
"Qty_Clean", SUM ( OPEN_OPPORTUNITY_EVOLUTION_C4C[Quantity] )
),
[Qty_Clean]
),
OPEN_OPPORTUNITY_EVOLUTION_C4C[Snapshot Date] = SnapshotDateSelected,
OPEN_OPPORTUNITY_EVOLUTION_C4C[Potential Close Date] >= MonthStart,
OPEN_OPPORTUNITY_EVOLUTION_C4C[Potential Close Date] <= CloseDateEnd,
FILTER (
'PRODUCT_STRUCTURE_C4C',
NOT AutomationSelected
|| NOT ( 'PRODUCT_STRUCTURE_C4C'[Model] IN {
"Automation Analysis", "Project", "Peripherals",
"Change Request", "LSI", "Software", "System Support"
} )
)
)
),

/* ── TOTAL / SUBTOTAL ROW ──
Iterate each distinct month visible in the current filter context
and SUM the per-month results so the total = sum of bar chart values */
SUMX (
VALUES ( 'CALENDAR'[Month] ), -- same column as IsMonthLevel check above
VAR MonthStart =
DATE ( YEAR ( MIN ( 'CALENDAR'[Date] ) ), MONTH ( MIN ( 'CALENDAR'[Date] ) ), 1 )
VAR MonthEnd = EOMONTH ( MonthStart, 0 )
VAR SnapshotDateSelected =
CALCULATE (
MAX ( OPEN_OPPORTUNITY_EVOLUTION_C4C[Snapshot Date] ),
OPEN_OPPORTUNITY_EVOLUTION_C4C[Snapshot Date] >= MonthStart,
OPEN_OPPORTUNITY_EVOLUTION_C4C[Snapshot Date] <= MonthEnd
)
VAR CloseDateEnd = EOMONTH ( MonthStart, 11 )
RETURN
IF (
ISBLANK ( SnapshotDateSelected ),
0,
CALCULATE (
SUMX (
SUMMARIZE (
OPEN_OPPORTUNITY_EVOLUTION_C4C,
OPEN_OPPORTUNITY_EVOLUTION_C4C[Opportunity Key],
OPEN_OPPORTUNITY_EVOLUTION_C4C[Sales Cycle Phase],
"Qty_Clean", SUM ( OPEN_OPPORTUNITY_EVOLUTION_C4C[Quantity] )
),
[Qty_Clean]
),
OPEN_OPPORTUNITY_EVOLUTION_C4C[Snapshot Date] = SnapshotDateSelected,
OPEN_OPPORTUNITY_EVOLUTION_C4C[Potential Close Date] >= MonthStart,
OPEN_OPPORTUNITY_EVOLUTION_C4C[Potential Close Date] <= CloseDateEnd,
FILTER (
'PRODUCT_STRUCTURE_C4C',
NOT AutomationSelected
|| NOT ( 'PRODUCT_STRUCTURE_C4C'[Model] IN {
"Automation Analysis", "Project", "Peripherals",
"Change Request", "LSI", "Software", "System Support"
} )
)
)
)
)
)

if possible share pbix

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.

Thanks,

Connect with me on:
LinkedIn |
Data With Pankaj - YouTube

[BalajiBuddha]

Hi Pankaj,

it is not showing selected months total values in table showing 0, i want to show total values in table, please see below screenshot!

Thanks in advance.