Forum Discussion
Getting text values with measure
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
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 ?
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,
3 Replies
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,
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 structureHi 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,
