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: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
2,984 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
2,969 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
2,964 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
2,960 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
2,952 Views
0
v-shex-msft
Community Support
Community Support
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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Report modified.zip
Message 6 of 7
2,926 Views
0
j_w
Helper IV
Helper IV
In response to v-shex-msft

‎02-21-2018 07:20 PM

@v-shex-msft

 

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
2,922 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

View All