Getting text values with measure

[Shivkanyabyale]

I want to create a measure wich gives me the text values from column "Name of the customer" 
Say I currently have 4 tables for my use 
Client Table- [Client]contain list of my clients
Sales- [Amount]contain sales of my all clients specificaaly which uses ZIP software 
Credit-{Amount] contain sales of my all clients specificaaly which uses ZIP software 
Sales tally- [Amount] contain sales of my all clients specificaaly which uses tally software (it inclues [sales-credit] as my sale amount

I used measure 

sales=SUM('Sales tally'[Amount])+(SUM(Sales[Amount])-SUM(Credit[Amount]))
to get my total sales from any customer 

I am creting visual to see the top 5 name of the customer of my seleted client from clientTable
but i have client in different tables sales tally and sales i can use sales with my sales measure but how to get the top 5 customers of the seleted client which are in different table with measure
i want to create this isual with help of measure it this possible to do ?

[DataNinja777]

Hi @Shivkanyabyale ,

If you need to create the visual without changing the current data structure, you can achieve this using DAX measures and relationships in your model.

Here is the solution:

First, create a measure to calculate total sales, combining values from all tables as you did:

Total Sales = 
    SUM('Sales tally'[Amount]) +
    (SUM(Sales[Amount]) - SUM(Credit[Amount]))

Next, create a measure to rank customers based on their sales. Since the customer names are in different tables, use UNION to bring them together virtually and calculate ranks.

Top 5 Customers = 
VAR CombinedData =
    UNION(
        SELECTCOLUMNS('Sales tally', "Customer", 'Sales tally'[Name of the customer], "SalesAmount", [Total Sales]),
        SELECTCOLUMNS(Sales, "Customer", Sales[Name of the customer], "SalesAmount", [Total Sales]),
        SELECTCOLUMNS(Credit, "Customer", Credit[Name of the customer], "SalesAmount", [Total Sales])
    )
VAR FilteredClient = FILTER(CombinedData, [Customer] IN DISTINCT('Client Table'[Client]))
VAR RankedData =
    ADDCOLUMNS(FilteredClient, "Rank", RANKX(FilteredClient, [SalesAmount], , DESC))
VAR TopCustomers =
    FILTER(RankedData, [Rank] <= 5)
RETURN
    CONCATENATEX(TopCustomers, [Customer], ", ")

This measure will combine the customer names from all relevant tables, calculate their total sales, rank them, filter the top 5, and return their names as a comma-separated string for display in the visual.

To create your visual:

Use the Client Table as the slicer for selecting a client.
Add the Top 5 Customers measure to a card or table visual.
This approach ensures that you dynamically fetch the top 5 customers for the selected client, even though the customer data is scattered across multiple tables.

Best regards,

[Shivkanyabyale]

Thank you so much for your response but i don't want to see top5 customers on scorecard , i want to create the cluster bar chart for top 5 cutomers 
is it possible with measure without changing data structure 

[DataNinja777 · Accepted solution]

Hi @Shivkanyabyale ,

To create a clustered bar chart for the top 5 customers in Power BI without changing the data structure, you can achieve this using DAX measures. First, create a measure for total sales, which aggregates data from the different tables. For example, you can define the Total Sales measure as:

Total Sales = SUM('Sales tally'[Amount]) + (SUM(Sales[Amount]) - SUM(Credit[Amount]))

Next, create a measure to rank customers based on their total sales. This measure will virtually combine data from the different tables and rank customers dynamically. For instance:

Customer Rank = 
VAR CombinedData =
    UNION(
        SELECTCOLUMNS('Sales tally', "Customer", 'Sales tally'[Name of the customer], "SalesAmount", [Total Sales]),
        SELECTCOLUMNS(Sales, "Customer", Sales[Name of the customer], "SalesAmount", [Total Sales]),
        SELECTCOLUMNS(Credit, "Customer", Credit[Name of the customer], "SalesAmount", [Total Sales])
    )
VAR FilteredClient = 
    FILTER(CombinedData, [Customer] IN DISTINCT('Client Table'[Client]))
VAR RankedData = 
    ADDCOLUMNS(FilteredClient, "Rank", RANKX(FilteredClient, [SalesAmount], , DESC))
RETURN
    RANKX(RankedData, [SalesAmount], , DESC)

After ranking the customers, create another measure to filter only the top 5 customers. This measure will return the total sales value for customers in the top 5 and return blank for others. Define this measure as:

Top 5 Sales = IF([Customer Rank] <= 5, [Total Sales], BLANK())

Finally, add the "Name of the customer" column to the axis of your clustered bar chart and use the Top 5 Sales measure as the value. This will ensure the chart dynamically shows only the top 5 customers based on their sales values.

Best regards,