Assigning a group from.

[Sahil5791]

I have 2 two tables in powerBI. They are not connected yet. 
1) Sales Table
2) Pricing Sheet

I also have calendar table connected with Sales table with one to many relationship. I have a requirement to create a table visual for customer and their sales. I have created a sales measure to calculate sales by a customer for the selected/current quarter. This dax is dynamic in nature and show sales for a customer based on slicer selection. 

Now i want to assigned the customer discount group based on the sales for selected/current quarter. For example a customer is of account type Neuro and has sales between 100-199 for current/selected quarter then the discount group should be 2N. I have account type column in sales table as well. As sales Range column in text i have created Min volume and Max volumn column so as to get integer values from the sale range

[Anonymous]

Hi @Sahil5791,

As we haven’t heard back from you, we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi @Sahil5791,

As we haven’t heard back from you, we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi @Sahil5791,

As we haven’t heard back from you, we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[burakkaragoz · Accepted solution]

Hi @Sahil5791 ,

You're almost there. Since your Assign table isn't directly related to the Calendar or Sales table, using a measure like the one below is a good approach. Here's a slightly refined version that might help with clarity and performance:

Sales by Assigned Group = 
VAR selectedDate = SELECTEDVALUE('Calendar'[Date])
RETURN
CALCULATE(
    SUM('Sales'[SalesAmount]),
    FILTER(
        'Assign',
        'Assign'[StartDate] <= selectedDate &&
        'Assign'[EndDate] >= selectedDate
    )
)

Make sure your visual includes the 'Group' column from the Assign table. This way, the measure will evaluate the sales amount for each group where the selected date falls between the start and end dates.

Also, if your Sales table has a group reference, you might need to adjust the logic to join that with Assign, or use TREATAS to simulate a relationship.

Let me know if you need help wiring the tables together or optimizing the model.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI

[johnbasha33]

@Sahil5791 

try this

CustomerDiscountGroup = 
VAR CurrentAccountType = SELECTEDVALUE(Sales[AccountType])
VAR CurrentCustomer = SELECTEDVALUE(Sales[Customer])
VAR CurrentSales = [SalesAmount]  -- your dynamic sales measure

VAR DiscountRow = 
    FILTER(
        PricingSheet,
        PricingSheet[AccountType] = CurrentAccountType &&
        CurrentSales >= PricingSheet[MinVolume] &&
        CurrentSales <= PricingSheet[MaxVolume]
    )

VAR Result = 
    SELECTEDVALUE(DiscountRow[DiscountGroup], "No Group")

RETURN
    Result

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[Sahil5791]

Thank you for the dax. It is working for now as there is only one quarter data in pricing sheet.just want to confirm and make sure the dax will dynamically adjust for chaning quarter meaning the discount group change on quarterly basis and is updated in the backend in the pricing sheet and it is an in incremental load. So this dax should detect the account type, quarter, sales and accordingly assign the discount group for the selected quater.