measure to group values

[TomSinAA]

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

[v-xinruzhu-msft · Accepted solution]

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.

[parry2k]

[parry2k]

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

[parry2k]

@TomSinAA try something like this:

M1 = SUMX ( YourTable, CALCULATE ( min(Data[A Pts] ) ) )

[TomSinAA]

That did not work.  It still shows 80

[Ashish_Mathur]

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]

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.

[Ashish_Mathur]

Hi,

Try these measures

A pt = MIN(Data[A Pts])

B pt = MIN(Data[B Pts])

A points = if(HASONEVALUE(Data[Color]),[A pt],SUMX(VALUES(Data[Color]),[A pt]))

B points = if(HASONEVALUE(Data[Color]),[B pt],SUMX(VALUES(Data[Color]),[B pt]))

Made points = SUM(Data[Made Pts])

Hope this helps.

[TomSinAA]

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.