Is it possible to create measures that display the unique value for a column in a table that has multiple repeating rows with the same values in the column and the measure uses another column to group by. If the data looks like this: StateColorIDPKDescriptionA PtsB PtsMade PtsReadyYellow3ABX-45Walking732ReadyYellow3ABX-45Walking731ReadyYellow3ABX-45Walking73NULLReadyYellow3ABX-45Walking73NULLReadyYellow3ABX-45Walking73NULLReadyBlue3ABX-46Running94NULLReadyBlue3ABX-46Running94NULLReadyBlue3ABX-46Running941ReadyBlue3ABX-46Running94NULLReadyBlue3ABX-46Running94NULL I want to be able create a measure for A pts and B pts columns that display the data like this:StateColorIDPKDescriptionA PtsB PtsMade PtsReadyYellow3ABX-45Walking733ReadyBlue3ABX-46Running941 So A pts and B pts columns are grouped by Description when rolled up into the summary table, but Made Pts column sums the total by Description. Then another table that displays the overall totals like this:StateA PtsB PtsMade PtsReady1674

Hi TomSinAA You can try the following mwasures A = VAR A = SUMMARIZE ( 'Table', [State], [Color], [PK], "a_min", MIN ( 'Table'[A Pts] ) ) RETURN SUMX ( FILTER ( A, [State] IN VALUES ( 'Table'[State] ) && [Color] IN VALUES ( 'Table'[Color] ) && [PK] IN VALUES ( 'Table'[PK] ) ), [a_min] ) B = VAR A = SUMMARIZE ( 'Table', [State], [Color], [PK], "b_min", MIN ( 'Table'[b Pts] ) ) RETURN SUMX ( FILTER ( A, [State] IN VALUES ( 'Table'[State] ) && [Color] IN VALUES ( 'Table'[Color] ) && [PK] IN VALUES ( 'Table'[PK] ) ), [b_min] ) Mode = VAR A = ADDCOLUMNS ( 'Table', "Mode", IF ( [Made Pts] <> "NULL", INT ( [Made Pts] ), 0 ) ) VAR b = SUMMARIZE ( A, [State], [Color], [PK], "Sum", SUMX ( FILTER ( a, [State] IN VALUES ( 'Table'[State] ) && [Color] IN VALUES ( 'Table'[Color] ) && [PK] IN VALUES ( 'Table'[PK] ) ), [Mode] ) ) RETURN SUMX ( FILTER ( b, [State] IN VALUES ( 'Table'[State] ) && [Color] IN VALUES ( 'Table'[Color] ) && [PK] IN VALUES ( 'Table'[PK] ) ), [Sum] ) Best Regards! Yolo Zhu If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

TomSinAA

Helper IV

3 years ago

Solved

measure to group values

Is it possible to create measures that display the unique value for a column in a table that has multiple repeating rows with the same values in the column and the measure uses another column to group by.

If the data looks like this:

State	Color	ID	PK	Description	A Pts	B Pts	Made Pts
Ready	Yellow	3	ABX-45	Walking	7	3	2
Ready	Yellow	3	ABX-45	Walking	7	3	1
Ready	Yellow	3	ABX-45	Walking	7	3	NULL
Ready	Yellow	3	ABX-45	Walking	7	3	NULL
Ready	Yellow	3	ABX-45	Walking	7	3	NULL
Ready	Blue	3	ABX-46	Running	9	4	NULL
Ready	Blue	3	ABX-46	Running	9	4	NULL
Ready	Blue	3	ABX-46	Running	9	4	1
Ready	Blue	3	ABX-46	Running	9	4	NULL
Ready	Blue	3	ABX-46	Running	9	4	NULL

I want to be able create a measure for A pts and B pts columns that display the data like this:

State	Color	ID	PK	Description	A Pts	B Pts	Made Pts
Ready	Yellow	3	ABX-45	Walking	7	3	3
Ready	Blue	3	ABX-46	Running	9	4	1

So A pts and B pts columns are grouped by Description when rolled up into the summary table, but Made Pts column sums the total by Description.

Then another table that displays the overall totals like this:

State	A Pts	B Pts	Made Pts
Ready	16	7	4

Need Help

Anonymous

3 years ago

Hi TomSinAA

You can try the following mwasures

A =
VAR A =
    SUMMARIZE ( 'Table', [State], [Color], [PK], "a_min", MIN ( 'Table'[A Pts] ) )
RETURN
    SUMX (
        FILTER (
            A,
            [State]
                IN VALUES ( 'Table'[State] )
                    && [Color]
                        IN VALUES ( 'Table'[Color] )
                            && [PK] IN VALUES ( 'Table'[PK] )
        ),
        [a_min]
    )

B =
VAR A =
    SUMMARIZE ( 'Table', [State], [Color], [PK], "b_min", MIN ( 'Table'[b Pts] ) )
RETURN
    SUMX (
        FILTER (
            A,
            [State]
                IN VALUES ( 'Table'[State] )
                    && [Color]
                        IN VALUES ( 'Table'[Color] )
                            && [PK] IN VALUES ( 'Table'[PK] )
        ),
        [b_min]
    )

Mode =
VAR A =
    ADDCOLUMNS (
        'Table',
        "Mode", IF ( [Made Pts] <> "NULL", INT ( [Made Pts] ), 0 )
    )
VAR b =
    SUMMARIZE (
        A,
        [State],
        [Color],
        [PK],
        "Sum",
            SUMX (
                FILTER (
                    a,
                    [State]
                        IN VALUES ( 'Table'[State] )
                            && [Color]
                                IN VALUES ( 'Table'[Color] )
                                    && [PK] IN VALUES ( 'Table'[PK] )
                ),
                [Mode]
            )
    )
RETURN
    SUMX (
        FILTER (
            b,
            [State]
                IN VALUES ( 'Table'[State] )
                    && [Color]
                        IN VALUES ( 'Table'[Color] )
                            && [PK] IN VALUES ( 'Table'[PK] )
        ),
        [Sum]
    )

Best Regards!

Yolo Zhu

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

measure to group values.pbix34 KB

9 Replies

parry2k
Super User
3 years ago
Ashish_Mathur
Super User
3 years ago
Hi,

Drag the first 4 columns to a Table visual and write these measures

M1 = min(Data[A Pts])

M2 = min(Data[B Pts])

M3 = sum(Data[Made Pts])

Hope this helps.
- TomSinAA
  Helper IV
  3 years ago
  That works for the body of the first visual titled: Summary by State, ID, Color, Description. But the total at the bottom is not correct. It should be 16 for A pts and 7 for B Pts. It does not work for the Summary by State visual.
  - TomSinAA
    Helper IV
    3 years ago
    Ok, here is a visual using the same structured data.
    
    I am using this measure as the:
    1.ReqBoardEpicEstReqPts = CONVERT(CALCULATE(Max('pbi Issue'[ReqBoardEpicEstimatedRequirementsPoints]),FILTER('pbi Issue',ISBLANK([IssueEpicLink])=True)),INTEGER)
    
    Why is the Mini Epic rown and grand Total diplaying as 50. I want them to add the values for the individual Req rows.
parry2k
Super User
3 years ago
TomSinAA try something like this:

M1 = SUMX ( YourTable, CALCULATE ( min(Data[A Pts] ) ) )
- TomSinAA
  Helper IV
  3 years ago
  That did not work. It still shows 80

parry2k

Super User

3 years ago

TomSinAA I think you need this measure , you can replicate it for others as well:

Measure A Pts = 
SUMX ( 
    SUMMARIZE ( 
        MyTable, 
        MyTable[State], 
        MyTable[Color], 
        MyTable[ID], 
        MyTable[PK], 
        MyTable[Description], 
        "@Min", MIN ( MyTable[A Pts] ) 
    ), 
    [@Min] 
)

Anonymous

Not applicable

3 years ago

Hi TomSinAA

You can try the following mwasures

A =
VAR A =
    SUMMARIZE ( 'Table', [State], [Color], [PK], "a_min", MIN ( 'Table'[A Pts] ) )
RETURN
    SUMX (
        FILTER (
            A,
            [State]
                IN VALUES ( 'Table'[State] )
                    && [Color]
                        IN VALUES ( 'Table'[Color] )
                            && [PK] IN VALUES ( 'Table'[PK] )
        ),
        [a_min]
    )

B =
VAR A =
    SUMMARIZE ( 'Table', [State], [Color], [PK], "b_min", MIN ( 'Table'[b Pts] ) )
RETURN
    SUMX (
        FILTER (
            A,
            [State]
                IN VALUES ( 'Table'[State] )
                    && [Color]
                        IN VALUES ( 'Table'[Color] )
                            && [PK] IN VALUES ( 'Table'[PK] )
        ),
        [b_min]
    )

Mode =
VAR A =
    ADDCOLUMNS (
        'Table',
        "Mode", IF ( [Made Pts] <> "NULL", INT ( [Made Pts] ), 0 )
    )
VAR b =
    SUMMARIZE (
        A,
        [State],
        [Color],
        [PK],
        "Sum",
            SUMX (
                FILTER (
                    a,
                    [State]
                        IN VALUES ( 'Table'[State] )
                            && [Color]
                                IN VALUES ( 'Table'[Color] )
                                    && [PK] IN VALUES ( 'Table'[PK] )
                ),
                [Mode]
            )
    )
RETURN
    SUMX (
        FILTER (
            b,
            [State]
                IN VALUES ( 'Table'[State] )
                    && [Color]
                        IN VALUES ( 'Table'[Color] )
                            && [PK] IN VALUES ( 'Table'[PK] )
        ),
        [Sum]
    )

Best Regards!

Yolo Zhu

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

measure to group values.pbix34 KB

Forum Discussion

measure to group values

9 Replies

Related content

Grouping Measures into categories

Grouping values from measures

Measure returning incorrect value

Fabric & Azure Data User Group Program Requirements & Benefits

Group values using a measure ?