Create title from a result out of existing Measure!

[shantupm5]

Hello, 

Please help me with a measure to auto populate the Title. 

Requirement is, i have a output result which is from existing Measure. It is as below. 

I need to get the Title which has Maximum Cost Per Travel from the existing Measure , and the title should be Title = "Maximum Cost per Travel by Customer CD".  

	Cost Per Travel
Customer AB	$100
Customer BC	$200
Customer CD	$500
Customer DE	$50

[Ashish_Mathur · Accepted solution]

Hi,

This measure should work

Measure = "Maximum cost per travel by " & CONCATENATEX(TOPN(1,Customers,[Cost per travel]),Customers[Customer Name],", ")

I have assumed the following

1. Cost per travel is a measure that you have written
2. Customer is a Table with Customer Name as a column in it.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

Measure = "Least cost per order Customer is "&" "&CONCATENATEX(TOPN(1,VALUES(Data[Customer]),[CPO],ASC),Data[Customer])

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

This measure should work

Measure = "Maximum cost per travel by " & CONCATENATEX(TOPN(1,Customers,[Cost per travel]),Customers[Customer Name],", ")

I have assumed the following

1. Cost per travel is a measure that you have written
2. Customer is a Table with Customer Name as a column in it.

Hope this helps.

[shantupm5]

Hi Ashish,

Many thanks, this is working. 

Similarly do we have a dax which can also show me 

1) Min value from an existing measure

2) Meadian value from an existing measure.

Your guidance will be highly appriciable.

Regards

Prsahnth

[Ashish_Mathur]

You are welcome.  If my previous reply helped, please mark that reply as Answer.

The Cost of travel measure should be modified.  Use the MIN and MEDIAN functions instead of SUM.

[shantupm5]

Hi Ashish,

From the solution you provided which helped me to identify the Top drivers

CONCATENATEX(TOPN(1,Customers,[Cost per travel]),Customers[Customer Name],", ")

Similar to about (TOPN) function, do we also have any function to identify bottom drivers and median drivers?

[Ashish_Mathur]

Hi,

One of the inputs of the TOPN function is the sort order.  Give that as ascending.

[shantupm5]

Hi Ashish,

I gave ASC order command but getting below error

[Ashish_Mathur]

Read about the TOPN function here - TOPN function (DAX) - DAX | Microsoft Learn.  The fourth argument is important.

[shantupm5]

Hi Ashish,

I an still not able to figure out a solution for this dax. Please can you help me with an example?

Regards

Prashanth

[Ashish_Mathur]

Hi,

Share some data (in a format that can be pasted in an MS Excel file) and show the expected result.

[shantupm5]

Hi Ashish,

Here is some raw data.

With the belowformula i am able to arrive the top first customer 

"Best-In-Class for Cost Per Order is "&" "&CONCATENATEX(TOPN(1,'Master file xlsx',[Cost Per Order]),'Master file xlsx'[Customer])

Similary the requirement is to pick the customer who is at least of cost per order (Customer-1 in the below example. Please advice

Customer	Cost Per Order
Customer-1	$45
Customer-2	$55
Customer-3	$65
Customer-4	$75
Customer-5	$85
Customer-6	$95
Customer-7	$145
Customer-8	$545
Customer-9	$845

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

Measure = "Least cost per order Customer is "&" "&CONCATENATEX(TOPN(1,VALUES(Data[Customer]),[CPO],ASC),Data[Customer])

Hope this helps.

[shantupm5]

Hello Ashish

The below measure is working well for me, but i am seeing another challange now.

The column [CPO] has few customers who's data is blank.

Hence the Measure is pulling all the customers name who's data is blank.

I tried IFBLANK function and its not working for me. Below is the measure i tried.

Measure = IF(ISBLANK(CPO),BLANK(),"Least cost per order Customer is "&" "&CONCATENATEX(TOPN(1,VALUES(Data[Customer]),[CPO],ASC),Data[Customer])

 Please guide

Measure = "Least cost per order Customer is "&" "&CONCATENATEX(TOPN(1,VALUES(Data[Customer]),[CPO],ASC),Data[Customer])

[Ashish_Mathur]

Hi,

Share the download link of the PBI file and show the problem and expected result clearly.

[shantupm5]

Hi Ashish

Sorry, i will not be able to share the PBI file due to security concerns.

Here is the issue, below table has 3 customers whos CPO data is blank.

Customer	CPO
Customer-1	$45
Customer-2	$55
Customer-3	$65
Customer-4	$75
Customer-5	$85
Customer-6	$95
Customer-7	$145
Customer-8	$545
Customer-9	$845
Customer-10
Customer-11
Customer-12

Earlier you suggested the measure below which we used tp identify the customer who has the least Cost per order. But since we have the blank customer with no data (Blank cells), the measure we used is actully showing all the customers who has blank data instead of actual customer who has least cost per order.

the measure se used (measure: "Least cost per order Customer is "&" "CONCATENATEX(TOPN(1,VALUES(Data[Customer]),[CPO],ASC),Data[Customer])

As a solution i tried using IF(ISBLANK) funtion but it did not work. 

IF(ISBLANK(CPO),BLANK(),"Least cost per order Customer is "&" "&CONCATENATEX(TOPN(1,VALUES(Data[Customer]),[CPO],ASC),Data[Customer])

Please advice

Regards

Prashanth

[Ashish_Mathur · Accepted solution]

Hi,

Does this measure work?

measure= "Least cost per order Customer is " & CONCATENATEX(TOPN(1,FILTER(VALUES(Data[Customer]),[CPO]>0),[CPO],ASC),Data[Customer])

[shantupm5]

Hello Ashish,

Thank you so much and you are been genius all this while and helpful.

So kind of you 🙂

This measure worked too 🙂

Regards

Prashanth

[Ashish_Mathur]

You are welcome.

[shantupm5]

This helped, thanks a lot Ashish 🙂

[Ashish_Mathur]

You are welcome.

[Ritaf1983]

Hi @shantupm5 
If I understand you correctly and you need to return the title for the customer with maximum cost, then:

1. Create a measure finding this customer:

MaxCostCustomerByCost =

    VAR MaxCost =

        MAX('table'[Cost Per Travel])

    RETURN

        CALCULATE(

            FIRSTNONBLANK('table'[Customer], 1),

            'table'[Cost Per Travel] = MaxCost,

            DISTINCT('table'[Customer])

        )

2. Create measure with text for your title:

Title = "Maximum Cost per Travel by " &[MaxCostCustomerByCost]

3 . Use your title measure wherever you need

Link to a Sample File 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly