Creating a custom measure concatenating category labels for each group that exceeds a threshold

[tbyberg]

This was requested and i am not quite sure if it is possible.

Here is a better picture of the scenario:

Sample data ...

Salesman	Customer	Amount	Type
Joe	Jerald	1500	Fertilizer
Joe	Jerald	1100	Seed
Joe	John	950	Fertilizer
Joe	Sam	3000	Crop
Mary	William	2500	Tech
Mary	Scott	1300	Fertilizer
Mary	Scott	150	Seed
Mary	Scott	1200	Tech

The end result, is they want to identify which Customers met a $1,000 in sales threshold -- for each type of product.

they want to concatenate those types that met the threshold into one field and show after the Types ... similar to below.

So don't show at the Salesman level (Joe and Mary)

Just the customers 

	Crop	Fertilizer	Seed	Tech	Threshold
Joe
Jerald		$       1,500	$  1,100		Fertilizer, Seed
John		$           950
Sam	$  3,000				Crop
Mary
Scott		$       1,300	$     150	$  1,200	Fertilizer, Tech
William				$  2,500	Tech

I tried flagging the ones that met the threshold with this:

only showing a value if it met the threshold of $1,000 and if it was filtered to the Customer level.

ThresholdMet =

COMBINEVALUES(" ",

IF(

IF (

ISFILTERED (data[Customer]) ,

CALCULATE(SUM(data[Amount]),

FILTER (data,data[Type]="Crop"))

)

> 1000, "Crop",""

),

IF(

IF (

ISFILTERED (data[Customer]) ,

CALCULATE(SUM(data[Amount]),

FILTER (data,data[Type]="Fertilizer"))

)

> 1000, "Fertilizer",""

),

IF(

IF (

ISFILTERED (data[Customer]) ,

CALCULATE(SUM(data[Amount]),

FILTER (data,data[Type]="Seed"))

)

> 1000, "Seed",""

),

IF(

IF (

ISFILTERED (data[Customer]) ,

CALCULATE(SUM(data[Amount]),

FILTER (data,data[Type]="Tech"))

)

> 1000, "Tech",""

)

)

That gave me the values i wanted to concatenate.

But from there, i got stuck trying to concatenate those results into a single column that could be displayed after the Types.  

It seems like we would need to create a custom summarization table to accomplish this.  And even if that can be done, my data source is AAS - which basically locks down the model from being able to add custom tables in desktop i believe.

Any ideas or other approaches i could take to solve this one?

[harshnathani]

Hi @tbyberg ,

You can try this as a measure

Threshold met =

var _a = IF (MAX('Table'[Amount]) > 1000 , 1,0)

var _b = If(_a =1 , CONCATENATEX(FILTER(ALL('Table'),'Table'[Customer] = MAX('Table'[Customer]) && 'Table'[Salesman] =MAX('Table'[Salesman])) ,'Table'[Type],","))

RETURN

_b

You can also create a Calculated Column

Threshold met Col =

var _a = IF ('Table'[Amount] > 1000 , 1,0)

var _b = If(_a =1 , CONCATENATEX(FILTER(ALL('Table'),'Table'[Customer] = EARLIER('Table'[Customer]) && 'Table'[Salesman] =EARLIER('Table'[Salesman])) ,'Table'[Type],","))

RETURN

_b

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[tbyberg]

Thanks for taking a look, harshnathani

your calcs give similar results to my ThresholdMet measure except the threshoold of $1,000 needs to be met per customer and Type.  Yours would match that with a slight modification to the var _a for meeting the threshold at that level.

If the Type does not meet the criteria for the customer, then it doesn't get added to that Threshold list.  like Crop for Scott   $150 does not meet the $1,000 critieria 

The issue now is with how they want to display the results ...

Start with pivot table form with Type in the columns like this ....

Now if i add ThresholdMet in the Values, it would repeat for each category rather than just be a column on the end.

This is format they would like it in ...

1 row for each customer -- showing amounts by Type  along with which types met the threshold 

	Crop	Fertilizer	Seed	Tech	Threshold
Joe
Jerald		$       1,500	$  1,100		Fertilizer, Seed
John		$           950
Sam	$  3,000				Crop
Mary
Scott		$       1,300	$     150	$  1,200	Fertilizer, Tech
William				$  2,500	Tech

This is a challenging part due to their desired layout.

I am thinking in order to do that we would need a custom table built with the products and the new threshold metric.

However, with an AAS data source, we cannot add custom tables in power bi desktop -- so far as i know.

But if there is a way to work this out without a custom table, I would be fine with that.

[harshnathani · Accepted solution]

Hi @tbyberg ,

You will need to create a new Table

Table 2 =

UNION(

'Table',

SUMMARIZE('Table','Table'[Salesman],'Table'[Customer],"Threshold",

CONCATENATEX(FILTER(ALL('Table'),'Table'[Customer] = EARLIER('Table'[Customer]) && 'Table'[Salesman] =EARLIER('Table'[Salesman]) && 'Table'[Amount] > 1000) ,'Table'[Type],","), "TM",CONCATENATE("Threshold Met",""))

)

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[tbyberg]

@harshnathani 

This is what i thought.

I figured we needed a new summary table to accomplish.

We cannot do this when my dataset is AAS.  Hopefully someday they will allow pulling in other data sources alongside an AAS dataset or published dataset.

Thanks for the summary table code!  It's nice to see how this could be solved with that approach.