Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Junaid11
Helper V
Helper V

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

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

afrrrgtt.PNG

Your help would be greatly appreciated.

Thank you

1 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 

 

speedramps_0-1656619663092.png

 

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 !

 
 

 

View solution in original post

8 REPLIES 8
speedramps
Super User
Super User

You forgot the picture  😀

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 

 

speedramps_0-1656619663092.png

 

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 !

 
 

 

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

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. 😎
 
 

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)

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.

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 )
 
 
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!
 

Hello @speedramps ,

I have put it now. You can check it 😀

Thanks Juaid11 for a very good picture.  

I have already sent you a solution.

😎

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.