Sum of value with Different Scenarios

[zileng]

Hi,

I need to calculate sum of value with these scenarios:

• If there's > 1 version, take the latest version
• If there's > 1 options, take Option 1.

Class	Version	Options	Value
A	1	NO	1,000.00
A	2	NO	2,000.00
B	1	Option 1	1,050.00
B	1	Option 2	1,100.00
C	1	NO	500.00
C	2	NO	600.00
C	3	NO	700.00

Sum = 2,000 + 1,050 + 700 = 3,750

Thank you.

Regards,

Zi

[v-lid-msft · Accepted solution]

Hi @zileng ,

We can create a measure to meet your requirement:

Total Value =
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[Class],
        "Sum",
        VAR maxVersion =
            CALCULATE ( MAX ( 'Table'[Version] ) )
        RETURN
            IF (
                CALCULATE (
                    DISTINCTCOUNT ( 'Table'[Options] ),
                    FILTER ( 'Table', 'Table'[Version] = maxVersion )
                ) = 1,
                CALCULATE (
                    SUM ( 'Table'[Value] ),
                    FILTER ( 'Table', 'Table'[Version] = maxVersion )
                ),
                CALCULATE (
                    SUM ( 'Table'[Value] ),
                    FILTER (
                        'Table',
                        'Table'[Version] = maxVersion
                            && 'Table'[Options] = "Option 1"
                    )
                )
            )
    ),
    [Sum]
)

Best regards,

[Anonymous]

The code below presumes that you data is sorted in the manner by version and then option.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYj9/EEPHwMBAz8BAKVYHImMEkzFClnGC6vEvKMnMz1MwhOg0xSVvBJY3ROh3RrbTFFUcbqMZqrgxTNwcKh4LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Class = _t, Version = _t, Options = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Class", type text}, {"Version", Int64.Type}, {"Options", type text}, {"Value", Int64.Type}}),
    AddFirst = Table.AddColumn(#"Changed Type", "First", each if [Options] <> "NO" then [Value] else null),
    AddLast = Table.AddColumn(AddFirst, "Last", each if [Options] = "NO" then [Value] else null),
    #"Grouped Rows" = Table.Group(AddLast, {"Class"}, {{"First", each List.First([First]), type number}, {"Last", each List.Last([Last]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Value", each List.First(List.RemoveNulls({[First],[Last]}))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"First", "Last"})
in
    #"Removed Columns"

[zileng]

Hi,

I am new to this. I don't understand the codes.