Don't summarize a measure field in the table visual

[j_w]

Want to replace two fixed columns (12MthUnit and 6MthUnit) with a dynamic measure field which links to a what-if parameter ([PrevMths Value]).

Before adding the measure field into the table, there are 5 records:

After adding the measure field, only one record left:

How to keep the 5 records after adding the measure field?

The measure is:

PrevMthsUnit = SUMX(Sales, IF([PrevMths Value] = 1, [PrevUnits01],
IF([PrevMths Value] = 2, [PrevUnits01] + [PrevUnits02],
IF([PrevMths Value] = 3, [PrevUnits01] + [PrevUnits02] + [PrevUnits03],
IF([PrevMths Value] = 4, [PrevUnits01] + [PrevUnits02] + [PrevUnits03] + [PrevUnits04],
IF([PrevMths Value] = 5, [PrevUnits01] + [PrevUnits02] + [PrevUnits03] + [PrevUnits04] + [PrevUnits05],
IF([PrevMths Value] = 6, [PrevUnits01] + [PrevUnits02] + [PrevUnits03] + [PrevUnits04] + [PrevUnits05] + [PrevUnits06],
IF([PrevMths Value] = 7, [PrevUnits01] + [PrevUnits02] + [PrevUnits03] + [PrevUnits04] + [PrevUnits05] + [PrevUnits06] + [PrevUnits07],
IF([PrevMths Value] = 8, [PrevUnits01] + [PrevUnits02] + [PrevUnits03] + [PrevUnits04] + [PrevUnits05] + [PrevUnits06] + [PrevUnits07] + [PrevUnits08],
IF([PrevMths Value] = 9, [PrevUnits01] + [PrevUnits02] + [PrevUnits03] + [PrevUnits04] + [PrevUnits05] + [PrevUnits06] + [PrevUnits07] + [PrevUnits08] + [PrevUnits09],
IF([PrevMths Value] = 10, [PrevUnits01] + [PrevUnits02] + [PrevUnits03] + [PrevUnits04] + [PrevUnits05] + [PrevUnits06] + [PrevUnits07] + [PrevUnits08] + [PrevUnits09] + [PrevUnits10],
IF([PrevMths Value] = 11, [PrevUnits01] + [PrevUnits02] + [PrevUnits03] + [PrevUnits04] + [PrevUnits05] + [PrevUnits06] + [PrevUnits07] + [PrevUnits08] + [PrevUnits09] + [PrevUnits10] + [PrevUnits11],
IF([PrevMths Value] = 12, [PrevUnits01] + [PrevUnits02] + [PrevUnits03] + [PrevUnits04] + [PrevUnits05] + [PrevUnits06] + [PrevUnits07] + [PrevUnits08] + [PrevUnits09] + [PrevUnits10] + [PrevUnits11] + [PrevUnits12], 0)))))))))))))

Thanks

[Zubair_Muhammad]

@j_w

Try this revision

PrevMthsUnit =
VAR mymeasure =
    SUMX (
        Sales,
        IF (
            [PrevMths Value] = 1,
            [PrevUnits01],
            IF (
                [PrevMths Value] = 2,
                [PrevUnits01] + [PrevUnits02],
                IF (
                    [PrevMths Value] = 3,
                    [PrevUnits01] + [PrevUnits02]
                        + [PrevUnits03],
                    IF (
                        [PrevMths Value] = 4,
                        [PrevUnits01] + [PrevUnits02]
                            + [PrevUnits03]
                            + [PrevUnits04],
                        IF (
                            [PrevMths Value] = 5,
                            [PrevUnits01] + [PrevUnits02]
                                + [PrevUnits03]
                                + [PrevUnits04]
                                + [PrevUnits05],
                            IF (
                                [PrevMths Value] = 6,
                                [PrevUnits01] + [PrevUnits02]
                                    + [PrevUnits03]
                                    + [PrevUnits04]
                                    + [PrevUnits05]
                                    + [PrevUnits06],
                                IF (
                                    [PrevMths Value] = 7,
                                    [PrevUnits01] + [PrevUnits02]
                                        + [PrevUnits03]
                                        + [PrevUnits04]
                                        + [PrevUnits05]
                                        + [PrevUnits06]
                                        + [PrevUnits07],
                                    IF (
                                        [PrevMths Value] = 8,
                                        [PrevUnits01] + [PrevUnits02]
                                            + [PrevUnits03]
                                            + [PrevUnits04]
                                            + [PrevUnits05]
                                            + [PrevUnits06]
                                            + [PrevUnits07]
                                            + [PrevUnits08],
                                        IF (
                                            [PrevMths Value] = 9,
                                            [PrevUnits01] + [PrevUnits02]
                                                + [PrevUnits03]
                                                + [PrevUnits04]
                                                + [PrevUnits05]
                                                + [PrevUnits06]
                                                + [PrevUnits07]
                                                + [PrevUnits08]
                                                + [PrevUnits09],
                                            IF (
                                                [PrevMths Value] = 10,
                                                [PrevUnits01] + [PrevUnits02]
                                                    + [PrevUnits03]
                                                    + [PrevUnits04]
                                                    + [PrevUnits05]
                                                    + [PrevUnits06]
                                                    + [PrevUnits07]
                                                    + [PrevUnits08]
                                                    + [PrevUnits09]
                                                    + [PrevUnits10],
                                                IF (
                                                    [PrevMths Value] = 11,
                                                    [PrevUnits01] + [PrevUnits02]
                                                        + [PrevUnits03]
                                                        + [PrevUnits04]
                                                        + [PrevUnits05]
                                                        + [PrevUnits06]
                                                        + [PrevUnits07]
                                                        + [PrevUnits08]
                                                        + [PrevUnits09]
                                                        + [PrevUnits10]
                                                        + [PrevUnits11],
                                                    IF (
                                                        [PrevMths Value] = 12,
                                                        [PrevUnits01] + [PrevUnits02]
                                                            + [PrevUnits03]
                                                            + [PrevUnits04]
                                                            + [PrevUnits05]
                                                            + [PrevUnits06]
                                                            + [PrevUnits07]
                                                            + [PrevUnits08]
                                                            + [PrevUnits09]
                                                            + [PrevUnits10]
                                                            + [PrevUnits11]
                                                            + [PrevUnits12],
                                                        0
                                                    )
                                                )
                                            )
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
RETURN
    IF ( ISBLANK ( mymeasure ), "", mymeasure )

[j_w]

Hi @Zubair_Muhammad

I tried your version, but the table visual took a long time to get the result (my version only around one second, but only one record left), and instead of having the 5 records, the table showed all records, as following:

Thanks

[Zubair_Muhammad]

@j_w

Could you share your file?

[j_w]

Hi @Zubair_Muhammad

Please download the zip file here

The csv files path is C:\Report

Thanks

[Anonymous]

Hi @j_w,

You can refer to below steps to achieve your requirement.

1. Enter query editor, duplicate sales table and use unpivot columns feature to convert table.

2. Save and exit to query editor, then use 'sales unpivot' to create sale expand table to add missed records.

Table formula:

Spoiler

Sale Expand = 
VAR list =
    CROSSJOIN (
        DISTINCT (
            SELECTCOLUMNS (
                'Sales Unpivoted',
                "ProductCode", [ProductCode],
                "LocationCode", [LocationCode]
            )
        ),
        VALUES ( PrevMths[PrevMths] )
    )
VAR remain =
    EXCEPT (
        list,
        DISTINCT (
            SELECTCOLUMNS (
                'Sales Unpivoted',
                "ProductCode", [ProductCode],
                "LocationCode", [LocationCode],
                "PrevMths", [Units]
            )
        )
    )
RETURN
    UNION (
        SELECTCOLUMNS (
            'Sales Unpivoted',
            "ProductCode", [ProductCode],
            "LocationCode", [LocationCode],
            "Units", [Units],
            "Value", [Value]
        ),
        SELECTCOLUMNS (
            remain,
            "ProductCode", [ProductCode],
            "LocationCode", [LocationCode],
            "Units", [PrevMths],
            "Value", 0
        )
    )

3. Write measure to calculate running unit total.

Runnig =
SUMX (
    FILTER (
        ALLSELECTED ( 'Sale Expand' ),
        [LocationCode] = SELECTEDVALUE ( 'Sale Expand'[LocationCode] )
            && [ProductCode] = SELECTEDVALUE ( 'Sale Expand'[ProductCode] )
            && [Units] <= MAX ( [Units] )
    ),
    [Value]
)

4. Use above table to create matrix viusal.

Regards,
Xiaoxin Sheng

[j_w]

@Anonymous

Your solution doesn't solve my initial two problems.

I should make my questions more clear:

For example, there are 10 products and 5 locations, then:

1) In the table visual there should be 10 * 5 = 50 records.

2) The value in the column PrevMthsUnit need to be dynamically changed according to the value of the what-if parameter PrevMths.

Thank you.