Calculating accurate sum of measure in table visual?

[Anonymous]

I have a visual that displays a sum from the latest available period before or up to what a user selects. The calculation works great for each row, but the default total was incorrect (it would only sum the values equal to the latest available period and not any before it).

How can I calculate a total that adds up to the data shown in the visual?

Here is the visual (removed some sensitive data):

"UserPD" is a user friendly version of CBIYrPd. The formula for the measure CBIYrPd is as follows for now:

CBI YrPd =
VAR comp =
    SELECTEDVALUE ( 'Table CBI'[Competitor Name] )
VAR zn =
    SELECTEDVALUE ( 'Table CBI'[Zone] )
VAR useryrpd =
    LOOKUPVALUE (
        UserPeriod[Ad YrPd],
        UserPeriod[UserPd], SELECTEDVALUE ( UserPeriod[UserPd] )
    )
VAR exyrpd =
    IF (
        ISBLANK ( zn ),
        999912,
        LOOKUPVALUE (
            'Competitor Status'[ExpYrPd],
            'Competitor Status'[Competitor Name], comp,
            'Competitor Status'[Zone], zn
        )
    )
VAR cbiyrpd =
    CALCULATE (
        MAX ( 'Table CBI'[Ad YrPd] ),
        'Table CBI'[Ad YrPd] <= SELECTEDVALUE ( UserPeriod[Ad YrPd] )
    )
RETURN
    IF ( exyrpd >= useryrpd && ISNUMBER ( zn ), cbiyrpd )

So I designed CBIYrPd to be blank at the total level, as before it was picking the latest period and only totaling the latest period. For the total I have the current formula:

CBI FL Sum =
VAR cbipd = [CBI YrPd]
RETURN
    IF (
        ISBLANK ( SELECTEDVALUE ( 'Table CBI'[Zone] ) ),
        SUM ( [FL Basket] ),
        SUMX ( FILTER ( 'Table CBI', 'Table CBI'[Ad YrPd] = cbipd ), [FL Basket] )
    )

The "Sum([FL Basket])" is just a placeholder for the total sum. The sum for the false result is for the non-total rows and is correct. How can I sum all the visible rows up for the total? The only thing I can think of is replicating the table and then summing the "FL/Comp Basket" columns, but I don't know how to refer to a column within a calculated table in a measure. This is as far as I've got on the calculated table... anything I try to do that references the "Test PD" column throws up an error basically saying "Test PD doesn't exist"

SUMMARIZECOLUMNS (
    'Table CBI'[Competitor Status],
    'Table CBI'[Competitor Name],
    'Table CBI'[Zone],
    "Test PD", CALCULATE ( MAX ( 'Table CBI'[Ad YrPd] ), 'Table CBI'[Ad YrPd] <= 202201 ),
   	)

If I could replicate this line: 

SUMX ( FILTER ( 'Table CBI', 'Table CBI'[Ad YrPd] = cbipd ), [FL Basket] )

But replace the cbipd variable with the [Test PD] column or an equivalent calculation I think I would be in business for the calculated table idea. But nothing seems to work.

Any help would be greatly appreciated.

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

As a  workaround , you can try to create another measure to modify the total：

measure_new =
VAR _new =
    ADDCOLUMNS ( 'Table', "_value", [yourMeasure_TheWrongTotal] )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Competitor Name] ),
        [yourMeasure_TheWrongTotal],
        SUMX ( _new, [_value] )
    )

If you still have problems, maybe you can take a look at these two articles, which explains this question in detail:
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

https://www.vahiddm.com/post/why-my-measure-returns-the-wrong-total

Best Regards,
Community Support Team _ Zeon Zheng

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

[lbendlin]

"but I don't know how to refer to a column within a calculated table in a measure"

Let's say the table variable is called a and the field is called b  then you would adress it like 

SUMX(a,[b])