Forum Discussion
Getting text values with measure
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,
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 structure
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,