Monthly Grouped Sum on Condition

[vkisa]

Hi, 

I need a measure under the circumstances and info below,

1.  Date column is hierarcical,
2.  I have slicers for Date and customerid on the page
3. The measure will calculate to commission2 according to commission amunts, if total monthly commission by customerids is equal or lower than zero than 0, otherwise is equal to total commission.

Existed Table

Let's say "customer" table    

Requested Measure that gives the commission column in the table below,

I need a measure because I have also tooltip card that shows total amounts,

Thank you,

VKisa

[Anonymous]

Hi  @vkisa ,

Here are the steps you can follow：

1. Create measure.

commission2 =
var _value=
SUMX(
    FILTER(ALL('Table'),    YEAR('Table'[date])=YEAR(MAX('Table'[date]))&&MONTH('Table'[date])=MONTH(MAX('Table'[date]))&&'Table'[customerid]=MAX('Table'[customerid])),
    [commission])
return
IF(
    _value<=0&&MAX('Table'[customerid])<>BLANK(),0,_value)

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[123abc]

Certainly! It looks like you want to create a measure in Power BI that calculates a conditional commission based on the total monthly commission for each customer. Here's a possible DAX measure for your requirement:

Commission_Measure =
VAR TotalCommission = CALCULATE(SUM('customer'[commission]))
RETURN
IF(TotalCommission <= 0, 0, TotalCommission)

This measure uses the CALCULATE function to sum up the 'commission' column for the specified customer. It then checks if the total commission is less than or equal to zero. If true, it returns 0; otherwise, it returns the total commission.

You can place this measure in your 'customer' table, and when you use it in visualizations or tooltips, it will provide the desired commission value based on your conditions.

Make sure to adjust table and column names based on your actual data model.

If you have a Date hierarchy and want to calculate the total commission on a monthly basis, you might need to incorporate time intelligence functions. For instance, you could use the TOTALMTD function to calculate the total commission for the current month.

Commission_Measure =
VAR TotalCommission = CALCULATE(SUM('customer'[commission]), TOTALMTD('Date'[Date]))
RETURN
IF(TotalCommission <= 0, 0, TotalCommission)

Adjust the 'Date' and 'Date'[Date] references based on your actual date table and column names.

Remember to replace table and column names with your actual data model details.

[vkisa]

Thank you for your message @123abc .

I would prefer to see a measure that calculates the commission2 by customerid. Because, I want to see the total commission2 numbers correct when any table doesn't include customerid column. 

[123abc · Accepted solution]

Certainly! If you want to calculate the commission2 by CustomerID, you can create a measure that sums the commission for each customer and then applies the conditions. Here's a modified measure for that purpose:

Commission2 =
CALCULATE(
SUM(Sales[Commission]),
ALLEXCEPT(Sales, Sales[CustomerID]),
Sales[Commission] > 0
) + 0

This measure uses the CALCULATE function to sum the commission for each CustomerID, considering all other filters in the context except for the CustomerID. The ALLEXCEPT function is used to remove all filters on the Sales table except for the CustomerID. Additionally, a condition is applied to consider only positive commission amounts.

If the total calculated commission is greater than zero, it will be returned; otherwise, 0 will be returned. The + 0 at the end is added to ensure that if the total commission is already zero, it won't be affected by the conditions.

This measure should provide the total commission2 by CustomerID based on the conditions you specified. You can use this measure in tables, visuals, or tooltips to display the desired results.