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,