sum of all the values with lowest order number in each category and date

[Junaid11]

Hello,

I have this table:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdBLCsAgDATQu7hW0JjoYcR1ly2F3r9aVPylUKHCY0wmBGGEFMd9Plf+AVTaKdAA6YLp5C/KIIBVTWZmWWYL/RiyjAod02BmrhsNWYVVZka/YW0DxzLfp42t0dTHvjXi6rAsG+oYF6VptJUtaa4kbsqdZ/PLCttHW5phGdU+4gs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [order = _t, category = _t, date = _t, value = _t, DAX_column_previous_value = _t, #".." = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"order", Int64.Type}, {"category", type text}, {"date", type date}, {"value", Int64.Type}, {"DAX_column_previous_value", Int64.Type}, {"..", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Index", "DAX_column_previous_value", ".."})
in
#"Removed Columns"

and I want to get the sum of all values by lowest order number by each category and date type. Like in below picture

group1 and date 24 the lowest order number is 1 so its value is 4. As shown in picture all this makes up 17 which should be divided by 4 which 17/4 =4.25

Your help would be greatly appreciated.

Thank you

[speedramps]

You forgot the picture  😀

[speedramps · Accepted solution]

Hi Junaid11

Well done on describing your problem so clearly and providing the example data ! 😀😀😀

I wish all users did that. I am sure that you will be become a very sucessful Power BI Developer.

If you ever need any more help them please quote @speedramps in you ticket and I will receive an automated notification. It is a joy to help a user that puts effort into their problem descriptions. I have just read 10 terrible descriptions by other users. 

Try this ...

Click here to download a solution 

Create 2 measures

Value for lowest order number =

VAR lowestordernumber = MIN(Facts[order])

RETURN

CALCULATE(

SELECTEDVALUE(Facts[value]),

Facts[order] = lowestordernumber)

Myanswer =

VAR mysummary =

SUMMARIZE(Facts,

Facts[category],

Facts[date],

"Min order number", MIN(Facts[order]),

"Lowest values", [Value for lowest order number]

)

RETURN

SUMX(mysummary,[Lowest values])

Then add a card visual to your reporting canvas and drag Myanswer to the car. It equals 17!

Now add a table visual to your reporting canvas and drag 

group, date, Min order and Myanswer to the table visual. The total equals 17!

Click here to download a solution 

How it works .....

The first measure gets the lowest value for a "context"

for example category, date, lowest order number.

However, that measure wont produce a grand total so we need the second measure

which creates a temporary summary table and then uses the SUMX iterator command

to sum up each value in summary table.  

Understand ???

I much prefer to teach someone DAX than just give a solution.  

Thanks for reaching out for help.

I have helped you, now please help me by giving kudos.

Remeber we are unpaid volunteers.

Click the thumbs up and accept as solution button. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !

[Junaid11]

Hello @speedramps ,

Isn't there a way to divide it y 4 as asked in the question. I have got the sum but want to divide it by 4 as there were four groups based on category and date for each different combination

[speedramps]

sorry, I though that divide by 4 was so easy that you could do that bit yourslef but here is the answer. Please click the thumbs up and accept as solution buttons. Thank you. 😎

Click here to download a solution 

Create 2 measures

Value for lowest order number =

VAR lowestordernumber = MIN(Facts[order])

RETURN

CALCULATE(

SELECTEDVALUE(Facts[value]),

Facts[order] = lowestordernumber)

Myanswer by 4 =

VAR mysummary =

SUMMARIZE(Facts,

Facts[category],

Facts[date],

"Min order number", MIN(Facts[order]),

"Lowest values", [Value for lowest order number]

)

RETURN

DIVIDE(

SUMX(mysummary,[Lowest values]),

4)

[Junaid11]

Hello @speedramps ,

Can we make it independent not hardcoded like 4 so if we get any combination like 7 so the value should automatically be divided by 7 or other option?

Thanks for your help alot.

[speedramps]

Hi agianJunaid11

To get and divide automatically any combination make the following changes highlighted in red.

Value for lowest order number =

VAR lowestordernumber = MIN(Facts[order])

RETURN

CALCULATE(

SELECTEDVALUE(Facts[value]),

Facts[order] = lowestordernumber)

Myanswer by combinations =

VAR mysummary =

SUMMARIZE(Facts,

Facts[category],

Facts[date],

"Min order number", MIN(Facts[order]),

"Lowest values", [Value for lowest order number]

)

VAR mycombinations =

COUNTROWS(mysummary)

RETURN

DIVIDE(

SUMX(mysummary,[Lowest values]),

mycombinations )

Click here to download a solution with 25 / 5 = 5 

How it works ...

We create a temporary variable called mycombinations that counts the number of combination in the temporary summary table.

Then we divide the sum of the lowest values by mycombinations 

I feel I have answered multiple probems in one ticket and deserve multiple kudos.

Please click the thumbs up and accept solution solution for each answer I have given in this thread.

Thank you!

[Junaid11]

Hello @speedramps ,

I have put it now. You can check it 😀

[speedramps]

Thanks Juaid11 for a very good picture.  

I have already sent you a solution.

😎