cancel
Showing results for
Did you mean: Frequent Visitor

Sum of value with Different Scenarios

Hi,

I need to calculate sum of value with these scenarios:

• 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

1 ACCEPTED SOLUTION  Community Support

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,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3  Community Support

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,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  Solution Specialist

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),
#"Grouped Rows" = Table.Group(AddLast, {"Class"}, {{"First", each List.First([First]), type number}, {"Last", each List.Last([Last]), type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"First", "Last"})
in
#"Removed Columns" Frequent Visitor

Hi,

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