How to merge small values into "other" in visual?

[ctoscher]

We have a chart where we can have hundreds of records.

A small sample for testing is below (value as percentage):

name	value
A	50
B	30
C	16
D	1
E	1
F	1
G	1

We can easily imagine this as a pie chart with the 1% elements nearly disappearing - only being merged into "other" once the slice gets too small (close to what we need, but not exactly - we don't always use pie charts).

The "important" elements are anything above 2%.

We can easily create a filter and hide those values, but then the chart is no longer accurate to the rest of the data, only relative to the data shown.

What we're trying to do is move anything <2% into a merged name like "other" and allow that to display.

End result is a table like this:

name	value
A	50
B	30
C	16
other	4

The point of complication is that the value column is a measure - not sure how to apply this logic on top of what we already have.

Thank you,

[v-kelly-msft]

Hi  @ctoscher ,

First create a calculated column as below:

name new = 
var _sum=CALCULATE('Table'[_value],FILTER(ALL('Table'),'Table'[name]=EARLIER('Table'[name])))
var _total=SUMX(ALL('Table'),'Table'[_value])
var _percent=DIVIDE(_sum,_total)
Return
IF(_percent<0.02,"other",'Table'[name])

Then create a measure as below:

Measure = SWITCH(SELECTEDVALUE('Table'[name new]),
"other",SUMX(FILTER(ALL('Measure = SWITCH(SELECTEDVALUE('Table'[name new]),
"other",SUMX(FILTER(ALL('Table'),'Table'[name new]="other"),[_value]),[_value])Table'),'Table'[name new]="other"),[_value]),[_value])

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[ctoscher]

Thank you, this is pretty close.

This works really well in the sample you provided, but I'm having an issue with my table column not being a valid option for the Earlier or resulting If statement.

My dataset comes from another pbix file and my _value column is a calculated column. No data is summarized (I specified against summarized data for the original data that I import).

So:

1. Data imported
2. Data _value calculated from existing fields (concentration calculation below) - I named my measure Concentration.
3. pbix saved and published
4. Import data from published pbix file
5. *apply logic* for "other" - all Concentration values below 2% (keeping filters in mind).

Here's our Concentration calculation:

Concentration = 
VAR balance = SUM(Concentration[Balance])
VAR total = CALCULATE(SUM(Concentration[Balance]), REMOVEFILTERS(Concentration))
RETURN DIVIDE(balance, total, 0)

The Concentration measure is the _value field you refer to. It's part of the original dataset (imported into the report file). Unfortunately, the logic you sent doesn't work against that dataset.

Any suggestions?

[v-kelly-msft]

Hi  @ctoscher ,

Is column "name" an ordinary column existing in your table?

Why you cant use "earlier"? My measure "_value" is a measure refering to your "value",not "Concentration",the result is as below：

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[ctoscher]

Hi Kelly,

When I use EARLIER it only gives me the option to use the Concentration measure.

I'd attach a sample, but won't let me, keeps saying it's not supported.

But if you create a table with the original data in my question, then add the Concentration measure, you'll see that you can't use the EARLIER function and that the scripts must be modified.

The only "selectable" table in PBIX is the new Concentration column - no original table fields can be used in that query.

Thank you,

[v-kelly-msft]

Hi  @ctoscher ,

I wanna test Concentration measure here,but I cant match the column "Balance",as there's no column with this name in your sample.

Pls upload your sample .pbix file to a Cloud Service then share a public link with us.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[ctoscher]

Hi Kelly, I'm not actually sure how to upload a document here. I don't see any options.

I can walk you through recreating the same file though, takes a minute.

Dataset

Create following table as data source:

client	debtor	balance
Z	A	50
Z	B	48
Z	C	1
Z	D	1
Y	E	50
Y	F	30
Y	G	1

Measures

Add the Concentration measure:

Concentration =
VAR balance = SUM('Table'[balance])
VAR total = CALCULATE(SUM('Table'[balance]), REMOVEFILTERS('Table'))
RETURN DIVIDE(balance, total, 0)

Add the name new measure.

This would be updated to the below based on new column names. This doesn't work in the IF or EARLIER statements (doesn't like Table[client]):

name new = 
var _sum=CALCULATE('Table'[Concentration],FILTER(ALL('Table'),'Table'[client]=EARLIER('Table'[client])))
var _total=SUMX(ALL('Table'),'Table'[Concentration])
var _percent=DIVIDE(_sum,_total)
Return
IF(_percent<0.02,"other",'Table'[client])

The idea here is that we would specify the client based on its name (using RLS) so that our clients only see the data associated with their account. So concentration is a reflection of only their data (but also allows our staff to see concentration across the board or with specific client filters using the same report).

Thank you,

Colton

[v-kelly-msft · Accepted solution]

Hi @ctoscher ，

Sorry for the late reply.

First create a dim table as below:

Dim = UNION(VALUES('Table'[debtor]),ROW("client","other"))

Then create 2 measures as below:

measure = IF('Table'[Concentration]<0.02,"other",SELECTEDVALUE('Table'[debtor]))

Measure 2 = 
var _table=CALCULATETABLE(VALUES('Table'[debtor]),FILTER(ALLSELECTED('Table'),'Table'[measure]="other"))
Return
IF(MAX('Dim'[debtor]) in _table,BLANK(),
SWITCH(MAX('Dim'[debtor]),
"other",CALCULATE(SUM('Table'[balance]),FILTER(ALLSELECTED('Table'),'Table'[measure]="other")),CALCULATE(SUM('Table'[balance]),FILTER(ALLSELECTED('Table'),'Table'[debtor]=SELECTEDVALUE(Dim[debtor])))))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[m3tr01d]

Hello,

SQLBI made a video on something really similar. You should take a lookt at it :
https://www.youtube.com/watch?v=nVvlEHKr_0o&ab_channel=EdBangerRecordsEdBangerRecordsVerified