Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
I have a table loaded with 60+ records and I would like to create a table that exibihts only the Top 10 and a 11th item showing the total for all 50 remaining records. Is that possible using a DAX? Sorry if it is a dumb question but I started using PB on last week so...
Thanks!
Solved! Go to Solution.
Hi @Acamara ,
DAX can do this but needs some preparatory steps first:
1. Add a new row name 'Remain records' in your initial table:
2. Add an index column in power query:
After these steps, my sample data table will look like this:
Now we can create a calculated table using the below dax formula:
test =
SUMMARIZE (
FILTER (
ALL ( 'Table' ),
'Table'[Index] <= 10
|| 'Table'[Record] = "Remain Records"
),
'Table'[Record],
"Result", IF (
[Record] <> "Remain Records",
[Record],
CALCULATE (
COUNT ( 'Table'[Record] ),
FILTER (
ALL ( 'Table' ),
'Table'[Index] > 10
&& 'Table'[Record] <> "Remain Records"
)
) & ""
)
)
Here is the result of the new table:
Attached my sample file that hopes to help you if I understood your goal correctly: How to create a Table with a Group of all values lower than top x.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Acamara ,
DAX can do this but needs some preparatory steps first:
1. Add a new row name 'Remain records' in your initial table:
2. Add an index column in power query:
After these steps, my sample data table will look like this:
Now we can create a calculated table using the below dax formula:
test =
SUMMARIZE (
FILTER (
ALL ( 'Table' ),
'Table'[Index] <= 10
|| 'Table'[Record] = "Remain Records"
),
'Table'[Record],
"Result", IF (
[Record] <> "Remain Records",
[Record],
CALCULATE (
COUNT ( 'Table'[Record] ),
FILTER (
ALL ( 'Table' ),
'Table'[Index] > 10
&& 'Table'[Record] <> "Remain Records"
)
) & ""
)
)
Here is the result of the new table:
Attached my sample file that hopes to help you if I understood your goal correctly: How to create a Table with a Group of all values lower than top x.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Worked. Thank you!
Hi @Acamara ,
You can create this in Power Query using group by function.
Below are steps:
1) create duplicate of current table
2) select column by which you want to summarize and choose group by option. Under value, choose sum of column for measure
3) for new table, sort by total measure descending and index column starting from 1
4) add conditional column "Criteria", if index <= 10 then index column else "Other"
5) group by columns Consumer_Id and Criteria
5) create new conditional column "Final text", if criteria <> "Other" then Consumer_Id, else "Other"
6) group by column "Final text"
Below are screenshots from my demo:
Cheers,
Nemanja
This is doable but tricky. One way would require addition of another table with values 1-10 and "Other" to be used as the rows on your table. It probably wouldn't be that flexible toward changes or adapting later if you are just starting out. I would encourage you to make a table that shows the top 10, and then a card that you placed below the table with a Measure called "Other" that give the sum of the not-Top 10. If that works, the community can provide example DAX.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |