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
StidifordN
Helper III
Helper III

Dynamic grouping of table visual based on slider parameter selection

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.

 

tempsnip.png

Parameter for the sliding bar value selection

Parameter for sliding bar value selectionParameter for 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.

4 REPLIES 4
TheoC
Super User
Super User

Hi @StidifordN 

 

I believe you want to add a new calculated column to get this style of output?

TheoC_0-1635837958415.png

 

 

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

StidifordN
Helper III
Helper III

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.

TheoC
Super User
Super User

Hi @StidifordN 

 

I believe you want to add a new calculated column to get this style of output?

TheoC_1-1635820544023.png

 

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

StidifordN
Helper III
Helper III

Output example

 

tempsnip1.png

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.