Cumulative % or to group by percentile?

[GiFin]

Hi, I'm going around in circles with this one. I want to group the customers with the lowest ticket volumes into one group, together making 40% of the overall total of ticket submissions. This is one step in what I want to come to, but I am stuck with how to add the lowest %s together until they come to 40% ot total. 
I
Does anyone have any suggestions please? this group

[MarkLaf · Accepted solution]

I think I got this working, at least with the test data I used. Any time you want a dynamic axis, you must create a new table that adds your dynamic axis values to the ootb values. I set up a helper table, a numeric range parameter for setting the % cutoff (called 'Lowest X'), and a measure to dynamically group customers under the cutoff.

Given the below:

CustomerTickets:

Customer	Tickets Submitted	Group
C1	89	A
C2	2	B
C3	32	C
C4	123	B
C5	32	A
C6	4	C
C7	34	A
C8	24	B
C9	34	A
C10	2	B
C11	2	C
C12	5	B
C13	54	A
C14	45	B
C15	54	C

I created this helper table with my grouping label ('Lowest X'):

CustomersDynRollup = 
UNION(
	SUMMARIZE(
		CustomerTickets,
		CustomerTickets[Customer],
		CustomerTickets[Tickets Submitted]
	),
	{
		("Lowest X", -1)
	}
)

CustomersDynRollup:

Customer	Tickets Submitted
C1	89
C2	2
C3	32
C4	123
C5	32
C6	4
C7	34
C8	24
C9	34
C10	2
C11	2
C12	5
C13	54
C14	45
C15	54
Lowest X	-1

* Be sure to set [Customer]'s Sort By to [Tickets Submitted]. Otherwise, the grouping will jump around on the pie chart.

And before we put the measure together, let's define the numeric range parameter that we'll use to change the target percentile with a slicer (MS doc: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-what-if😞

i) In Report view, go to Modeling ribbon

ii) Click the New parameter dropdown and then Numeric range

Note: field parameters (the options other than numeric) is a preview feature, so your UI may look a little different from the above

iii) Create your numeric range with the following setup (Name: Lowest X, Data type: Decimal number, Minimum: 0, Maximum: 1, Increment: .01, Default: 0.4)

iv) Power BI will then automatically create a single column table with the specified values, which you can use in a slicer. Additionally, PBI automatically creates a measure for referencing the selected value if it exists but otherwise using your specified default. If you created the parameter with the above inputs, this measure will be the [Lowest X] that we use in the below measure.

And here is the measure.

Low by Val = 
VAR _running =
GENERATE(
	ALLSELECTED(CustomerTickets),
	ROW(
		"RunningSum", DIVIDE(
			CALCULATE(
				SUM(CustomerTickets[Tickets Submitted]),
				WINDOW( 1, ABS, 0, REL,
					ALLSELECTED(
						CustomerTickets[Customer],
						CustomerTickets[Tickets Submitted]
					),
					ORDERBY(
						CustomerTickets[Tickets Submitted],
						ASC
					)
				),
				REMOVEFILTERS(CustomerTickets)
			),
			CALCULATE(
				SUM(CustomerTickets[Tickets Submitted]),
				ALLSELECTED(CustomerTickets)
			)
		)
	)
)
VAR _rowsAboveThreshold = FILTER(
	_running,
	[RunningSum] > [Lowest X Value]
)
VAR _rowsBelowThreshold = FILTER(
	_running,
	[RunningSum] <= [Lowest X Value]
)
RETURN
CALCULATE(
	SUM(CustomerTickets[Tickets Submitted]),
	_rowsAboveThreshold,
	TREATAS(
		VALUES(CustomersDynRollup[Customer]),
		CustomerTickets[Customer]
	)
)
+ IF(
	"Lowest X" IN VALUES(CustomersDynRollup[Customer]),
	CALCULATE(
		SUM(CustomerTickets[Tickets Submitted]),
		_rowsBelowThreshold
	)
)

Now for a pie chart, we can put CustomersDynRollup[Customer] in the Legend, and the measure [Low by Val] in the Values. CustomerTickets slicers should work on CustomersDynRollup because of the virtual relationship in the measure. Example next to a CustomerTickets table with some calcs to double-check the measure in the pie chart is working.

*Edit: included instructions for adding numeric range paramater

[GiFin]

Hi @MarkLaf  thank you so much! Sorry for the delay in responding, I had access issues. 
This looks amazing and I will try it out - but stupid question first - where do I set 

[Lowest X Value]

I don't get that part

[MarkLaf]

Yes, realizing I sort of skipped over, "I set up a helper table, a numeric range parameter for setting the % cutoff (called 'Lowest X'), and a measure to dynamically group customers under the cutoff." (emphasis added). Numeric range parameters are described here: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-what-if

By way of a quick walkthrough, to add the numeric range parameter:

i) In Report view, go to Modeling ribbon

ii) Click the New parameter dropdown and then Numeric range

Note: field parameters (the options other than numeric) is a preview feature, so your UI may look a little different from the above

iii) Create your numeric range with the following setup (Name: Lowest X, Data type: Decimal number, Minimum: 0, Maximum: 1, Increment: .01, Default: 0.4)

iv) Power BI will then automatically create a single column table with the specified values, which you can use in a slicer. Additionally, PBI automatically creates a measure for referencing the selected value if it exists but otherwise using your specified default. This measure is the [Lowest X] you were asking about.

I'll add the above info to my original response so it's more complete.

Will take a look at your other response in a bit.

[GiFin]

Wow! Thank you @MarkLaf ! Now got this working now on a pie with customer name & amt of tkts with slider 😍. Seems these are separate to the rest of the data though in this new table. My real data set is a lot larger, and want to select this lowest X % in order to analyze it - who are these customers, profiles etc. so I need to keep a relationship with the other data. I made a relationship in the model with cust name but then the slider functionality is lost 😞 Am I being too pushy with this extra bit? Thank you again for what you have shown though - incredibly helpful!

[MarkLaf]

Share what your other tables are related, what those relationships are (eg Customers -1---M-> Some other table), and what behavior you are trying to get vis a vis numeric slider and other visuals. Some dummy data is always helpful, too. We can see what we can do, then.

Also, I just noticed in your snip from your other post that you have a relationship set up between CustomersDynRollup and TicketData. The measure I provided is actually written to work without a relationship set up, so you should get rid of that. Perhaps that is causing the issue with the other relationships?

[GiFin]

Hi @MarkLaf, thanks again. 
I removed that relationship indeed. I'm sharing another image below. Essentially I would like to click on the lowest X group and see who those customers are. For now in the dummy data tht just means showing the Customer name, no. of tickets, the service they have, type and duration of tickets. 
At the moment when I click on Lowest X is acts like a separate group which I can't look into further. If I put more visuals around it, starting with a table just to see what's selected, it's doesn't seem to keep the customer name assocated with the Lowest X group as per the pie chart. I hope the image shows better what I mean. 
I don't see a way to share the file or the data here, is that possible? 

[MarkLaf]

As mentioned previously, we are leveraging virtual relationships to control how the visuals behave, so if we want visual filtering to go in the other direction, we have to put together a different measure to enforce it.

Lowest X Filter = 
CALCULATE( 
    IF( NOT ISEMPTY( CustomersDynRollup ), 0 ), 
    TREATAS( VALUES( CustomerTickets[Customer] ), CustomersDynRollup[Customer] ), 
    VALUES( CustomersDynRollup[Customer] ) 
)
+ 
IF( 
    "Lowest X" IN VALUES( CustomersDynRollup[Customer] ), 
    VAR custSet =  CALCULATETABLE( 
        ALLSELECTED( CustomerTickets ), 
        ALL( CustomerTickets[Customer], CustomerTickets[Tickets Submitted] ) 
    )
    VAR running = CALCULATE( 
        SUM( CustomerTickets[Tickets Submitted] ), 
        WINDOW( 1, ABS, 0, REL, custSet, ORDERBY( CustomerTickets[Tickets Submitted], ASC ) ), 
        REMOVEFILTERS( CustomerTickets ) 
    ) 
    VAR perct = DIVIDE( 
        [Running Total], 
        CALCULATE( SUM( CustomerTickets[Tickets Submitted] ), custSet ) 
    )
    RETURN
    IF( NOT ISBLANK( perct ) && perct <= [Lowest X Value], 0 )
)

Put this in the filter pane of your table visual (no need to add in the Columns well) and then set the filter to 'is not blank'

Output:

Also, perhaps too belatedly, I do want to point to a much simpler but more static approach to all of this. If you only need to group by 40% and don't expect to need to slice customers in any way (eg. where "Lowest 40%" has a different meaning when it's out of all customers vs out of all customers within some group you are slicing on), then you simply hard-code this logic into your original table. You'll need two columns: 1) either provide customer name or put under some "lowest label", 2) provide the sort values to ensure that the "lowest label" always stays at end of a visual when sorted by the "lowest label" column

The DAX for these two calculated columns would be:

Lowest 40% Group = 
VAR run = CALCULATE( 
    SUM( CustomerTickets[Tickets Submitted] ), 
    WINDOW( 1, ABS, 0, REL, ORDERBY( CustomerTickets[Tickets Submitted], ASC ) ), 
    REMOVEFILTERS( CustomerTickets ) 
)
VAR perc = DIVIDE( 
    run, 
    CALCULATE( SUM( CustomerTickets[Tickets Submitted] ), ALL( CustomerTickets ) ) 
)
RETURN
IF( perc <= .4, "Lowest 40%", CustomerTickets[Customer] )


Lowest 40% Group Sort = 
VAR run = CALCULATE( 
    SUM( CustomerTickets[Tickets Submitted] ), 
    WINDOW( 1, ABS, 0, REL, ORDERBY( CustomerTickets[Tickets Submitted], ASC ) ), 
    REMOVEFILTERS( CustomerTickets ) 
)
VAR perc = DIVIDE( 
    run, 
    CALCULATE( SUM( CustomerTickets[Tickets Submitted] ), ALL( CustomerTickets ) ) 
)
RETURN
IF( perc <= .4, -1, CustomerTickets[Tickets Submitted] )

Then, all you need to do is set the Sort By of [Lowest 40% Group] to [Lowest 40% Group Sort]. You can then drop the calculated column into your pie chart with your ticket aggregation (sort by calculated column, not agg), and drop your vanilla customer column and ticket agg into a table:

By way of a quick breakdown on these two approaches:

Calculated Columns	Helper Table + Virtual Relationship
+ Very simple + "Just works" with other data in your customer table - Static cumulative threshold - Static "lowest label" assignment - does not recalculate based on slicers	+ Allows for dynamic cumulative % grouping + Handles dynamic calculation of cumulative % so slicers on other Customer columns work - Complex compared to other approach - Requires defining new virtual relationships to handle any visual interactions

[GiFin]

Hi @MarkLaf  you've gone far above and beyond what I was expecting by way of help here. Hopefully this also helps others, no doubt it will. 
THANK YOU!!!

[GiFin]

Here is a view on what I'm working with:diff parts

 

[GiFin]

Thank you @Rupak_bi for this! I am getting somewhere now but not there yet. I need to add the %TicketCount field of the lowest volume customers until that comes to 40% of the grand total of the ticket volume submitted by the customers in the selection - so this would be dynamic because it may change depending on which customers are selected. So I'm not sure if a calculated column would be the way to go? But I now have rank thanks to you, so I am a bit further. I will try to adjust to add the % vol (%TicketCount) instead of Count of Ticket Number. Will come back. 

[Rupak_bi]

Hi @GiFin 

Then you need to do the same thing in a measure. But that will be a bit tricky as your axis will be dynamic. Please share good amount of sample data with desired output so that it can be worked out

[Rupak_bi]

Hi @GiFin ,

Follow below steps,

1. Create a calculated column (index) to rank the nos. of tickets smallest to largest. RANKX(table,ticket,Asc,Dens)

2. Create another column (running total)to get running total of the tickets based on the index. calculate(sum(tickets),all(table),index<=earlier (index))

3. create another column to make new axis based on 40%
new axis = 

var other_category = calculate(sum(tickets),all(table))*40%
return

if(running total<other_category,"Other category),customer)

4 . now use this new axis in the chart.

please accept this as solution if this works, else, share sample data in plain text to workout.