Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have data as shown below and I want to sum the amounts by id as shown in Expected sum column
Id | Amount | Expected sum |
1 | 100 | 3300 |
1 | 1200 | |
1 | 100 | |
1 | 400 | |
1 | 200 | |
1 | 1300 | |
2 | 300 | 1300 |
2 | 1000 | |
3 | 300 | 500 |
3 | 200 |
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.
Category | Expected_measure |
Under 1000 | 500 |
Over 1000 | 4600 |
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.
Category | Sum_measure |
Under 1000 | 1600 |
Over 1000 | 3500 |
It should show the categorization after summing the amounts by id.
How do I achieve my expected output?
Solved! Go to Solution.
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.
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.
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:
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.
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.
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.
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.
Here is my data
Supervisor | Id | Amount |
A | 1 | 100 |
A | 1 | 1200 |
A | 1 | 100 |
B | 1 | 400 |
B | 1 | 200 |
C | 1 | 1300 |
C | 2 | 300 |
D | 2 | 1100 |
D | 3 | 300 |
D | 3 | 200 |
and I want to show the breakdown like this. How do I achieve this?
Under 1000 | A | 200 |
Under 1000 | B | 600 |
Under 1000 | C | 300 |
Under 1000 | D | 500 |
Over 1000 | A | 1200 |
Over 1000 | B | |
Over 1000 | C | 1300 |
Over 1000 | D | 1100 |
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.
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.
@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.
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.
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.
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.
@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])
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
96 | |
90 | |
79 | |
67 |
User | Count |
---|---|
151 | |
121 | |
111 | |
107 | |
95 |