Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Morning all,
Fingers crossed this is a simple one - i can't seem to type the right words when searching to find the correct solution.
Dynamic Grouping of table visual output based on slider parameter.
I have a table visual with a simple list of suppliers and their spend (filtered by a slicer for a date range).
The end goal is to have the sliding bar 'Roll up suppliers below' able to be manipulated and the table below change based on that value selection.
The value selection, $5,000,000 for example, will set the tolerance value for is suppliers are listed (they have a total spend equal to or higher than the parameter value) and all other suppliers are grouped together into a single line that either reads "Supplier Spend below $5,000,000" or simply "Supplier Spend below tolerance"
The output for the below example will show 7 suppliers and their corresponding spend values, and then an 8th item with "Supplier Below..." and the total of all those suppliers.
Parameter for the sliding bar value selection
Many thanks for any assistance in either directing me to a solution already existing for this, or explaining a pathway to success.
Cheers
Neil.
Hi @StidifordN
I believe you want to add a new calculated column to get this style of output?
If so, just create a new calculated column to SUM the total amount by the relevant category:
Calc Column = CALCULATE ( SUM ( 'Table'[SalesAmount] ) , FILTER ( ALL ( 'Table' ) , Table[Customer] = EARLIER ( Table[Customer] ) ) )
Once you have put together the Calc Column, please use that as the column in your Slicer visual. This will then filter the vendor / customer by the total amount of spend allocated to them 🙂
Hope it helps.
Theo
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
As a shortcut I have completed a 'Top N' version per the below link.
https://community.powerbi.com/t5/Desktop/Top-10-Other/m-p/52120#M20971
But if anyone has a working solution for the value based outcome originally sought please do respond.
FYI - the Total Spend AU is a measured sum of transactional procurement values.
Hi @StidifordN
I believe you want to add a new calculated column to get this style of output?
If so, just create a new calculated column to SUM the total amount by the relevant category:
Calc Column = CALCULATE ( SUM ( 'Table'[SalesAmount] ) , FILTER ( ALL ( 'Table' ) , Table[Customer] = EARLIER ( Table[Customer] ) ) )
Once you have put together the Calc Column, please use that as the column in your Slicer visual. This will then filter the vendor / customer by the total amount of spend allocated to them 🙂
Hope it helps.
Theo
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Output example
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |