Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
j_w
Helper IV
Helper IV

Don't summarize a measure field in the table visual

‎02-04-2018 05:36 PM

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:

image.png

After adding the measure field, only one record left:

 

image.png

 

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

Labels:
Message 1 of 7
3,207 Views
0
6 REPLIES 6
Zubair_Muhammad
Community Champion
Community Champion

‎02-04-2018 11:19 PM

@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 )

Regards
Zubair

Please try my custom visuals
Message 2 of 7
3,192 Views
0
j_w
Helper IV
Helper IV
In response to Zubair_Muhammad

‎02-06-2018 02:33 AM

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:

Loading measure field, took a long time.pngNot only 5 records.png

 

Thanks

Message 3 of 7
3,187 Views
0
Zubair_Muhammad
Community Champion
Community Champion
In response to j_w

‎02-06-2018 02:49 AM

@j_w

 

Could you share your file?


Regards
Zubair

Please try my custom visuals
Message 4 of 7
3,183 Views
0
j_w
Helper IV
Helper IV
In response to Zubair_Muhammad

‎02-06-2018 02:42 PM

Hi @Zubair_Muhammad

 

Please download the zip file here

 

The csv files path is C:\Report

 

Thanks

Message 5 of 7
3,175 Views
0
Anonymous
Not applicable
In response to j_w

‎02-21-2018 06:13 PM

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.

15.PNG

 

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
        )
    )

16.PNG

 

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.

17.PNG

 

Regards,
Xiaoxin Sheng

Report modified.zip
Message 6 of 7
3,149 Views
0
j_w
Helper IV
Helper IV
In response to Anonymous

‎02-21-2018 07:20 PM

@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.

Message 7 of 7
3,145 Views
0

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

View All