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
bml123
Post Patron
Post Patron

Sum of amount by id and categorize after summing

 

I have data as shown below and I want to sum the amounts by id as shown in Expected sum column

IdAmountExpected sum
11003300
11200 
1100 
1400 
1200 
11300 
23001300
21000 
3300500
3200 

and I want to sum the amounts by Id and show it in categories as below and this is my expected output. It should show the categorization after summing the amounts by id.

CategoryExpected_measure
Under 1000500
Over 10004600

But I am getting like this when I used a simple SUM measure. It is summing all under 1000 and over 1000 separately and giving this output which is not my expected output.

CategorySum_measure
Under 10001600
Over 10003500

It should show the categorization after summing the amounts by id.

How do I achieve my expected output?

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi, @bml123 

Please check the below picture and the sample pbix file's link down below.

I have created a separate table, that is the category table looks like below.

 

Picture3.png

 

 

Sum by Category =
SUMX (
FILTER (
VALUES ( Data[Id] ),
COUNTROWS (
FILTER (
Categories,
CALCULATE ( SUM ( Data[Amount] ) ) >= Categories[Min]
&& CALCULATE ( SUM ( Data[Amount] ) ) < Categories[Max]
)
) > 0
),
CALCULATE ( SUM ( Data[Amount] ) )
)

 

 

https://www.dropbox.com/s/phh5i6rv7mgvsw9/bml.pbix?dl=0 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

12 REPLIES 12
bml123
Post Patron
Post Patron

@Jihwan_Kim Thank you so much. It worked perfectly.

v-xiaotang
Community Support
Community Support

Hi @bml123 

You also can try this.

create the measures:

sum_by_ID = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Id]=SELECTEDVALUE('Table'[Id])))
M_category = 
var _over1000= CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),[sum_by_ID]>1000))
var _under1000=CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),[sum_by_ID]<=1000))
return 
if(SELECTEDVALUE(Category[Category])="Over 1000",_over1000,_under1000)

 Result:

v-xiaotang_0-1620702342458.png

 

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

 

Jihwan_Kim
Super User
Super User

Hi, @bml123 

Please check the below picture and the sample pbix file's link down below.

I have created a separate table, that is the category table looks like below.

 

Picture3.png

 

 

Sum by Category =
SUMX (
FILTER (
VALUES ( Data[Id] ),
COUNTROWS (
FILTER (
Categories,
CALCULATE ( SUM ( Data[Amount] ) ) >= Categories[Min]
&& CALCULATE ( SUM ( Data[Amount] ) ) < Categories[Max]
)
) > 0
),
CALCULATE ( SUM ( Data[Amount] ) )
)

 

 

https://www.dropbox.com/s/phh5i6rv7mgvsw9/bml.pbix?dl=0 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


@Jihwan_Kim 

I have to show the same breakdown by Supervisor level.  Can you please let me know how do I acheieve that?

Hi, @bml123 

In the sample file, I only can see Id, amount, and category. 

Can you please let me know how to break it down by supervisor level?

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


@Jihwan_Kim 

Here is my data

SupervisorIdAmount
A1100
A11200
A1100
B1400
B1200
C11300
C2300
D21100
D3300
D3200

 

and I want to show the breakdown like this.  How do I achieve this?

 

Under 1000A200
Under 1000B600
Under 1000C300
Under 1000D500
Over 1000A1200
Over 1000B 
Over 1000C1300
Over 1000D1100

Hi, @bml123 

Please check the below link.

I am not sure if I understood your table correctly, but because the logic is a little different than the first question's logic, so I needed to add an index column to differentiate the first row and the third row.

 

Picture2.png

 

https://www.dropbox.com/s/phh5i6rv7mgvsw9/bml.pbix?dl=0 

 

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


@Jihwan_Kim Thank you for the response. I would like to show the details when right clicked and drill through is selected.  As the categories table is not connected to any other table in the data model, how can we show the correct details when drill through is clicked

Hi, @bml123 

I am not sure if I understood your question correctly.

Please check the below link.

 

Picture1.png

 

https://www.dropbox.com/s/phh5i6rv7mgvsw9/bml.pbix?dl=0 

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


@Jihwan_Kim 

In your first table, if I add supervisor, and if I right click on supervisor, it should take me to the details page and show the details of that supervisor only. Is that possible to do? the details page and category table are not related. Not sure if that is possible.

 

is there a way to create the measures in the details table itself without creating them in a separate category table so that it is easier to drill through to the details page.

@Jihwan_Kim 

is there a way to create the measures in the details table itself without creating them in a separate category table so that it is easier to drill through to the details page.

Fowmy
Super User
Super User

@bml123 

Create two measures for Under and Above 1000 and insert them in a matrix and turn on "Show on Rows" in the properties.

 

Above 1000 = 

var __IdAmount = 
    ADDCOLUMNS(
        SUMMARIZE( Table6, Table6[Id] ),
        "Total", CALCULATE(SUM(Table6[Amount]))
    )
return
	SUMX(__IdAmount, ([Total] >= 1000 ) * [Total]) 
Under 1000 = 

var __IdAmount = 
    ADDCOLUMNS(
        SUMMARIZE( Table6, Table6[Id] ),
        "Total", CALCULATE(SUM(Table6[Amount]))
    )
return
	SUMX(__IdAmount, ([Total] < 1000 ) * [Total]) 

 

 

Fowmy_0-1620515362043.png



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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.