Summing on Multiple Criteria When Values May be Duplicated For Some Data

[JlmillerRedmond]

I have annual sales data by month with customer, customer segment and product information.  The customer segment in the source data rolls up to summary segments that are pulled into the sales data from a related table (shown as Related Segment in the visual below).  Because more than one segment in the source data maps to each summary segment, I'm having a hard time summing sales data using DAX.  Once summed, I need to count the number of times a customer, month, product combination occur each month to be able to produce the correct results in output.

Here is a simplified table showing the issue:

How can I sum Units Sold using Month, Customer, Related Segment, and Product as criteria?  Then, how can I count the number of times the same criteria occur?  The count will be divided by the sum to produce a basis for later reporting since duplicate Related Segments will be added on each line where that segment appears.  In reporting, SumFix will be multilied by the count to return the total across instances that occur on multiple lines.

[DataNinja777 · Accepted solution]

Hi @JlmillerRedmond ,

To calculate the corrected SumFix in Power BI based on your provided Excel table, we need to replicate the logic for Columns U, V, and W using DAX measures. The task involves summing Units Sold based on the unique combination of Month, Customer, Related Segment, and Product, counting how many times each combination occurs, and then dividing the sum by the count to get the correct result. In Excel, Column U counts the occurrences of each combination using COUNTIFS, which can be translated into a DAX measure as follows:

Count of Related Segment =
CALCULATE(
    COUNTROWS(Sales),
    FILTER(
        Sales,
        Sales[Month] = EARLIER(Sales[Month]) &&
        Sales[Customer] = EARLIER(Sales[Customer]) &&
        Sales[Related Segment] = EARLIER(Sales[Related Segment]) &&
        Sales[Product] = EARLIER(Sales[Product])
    )
)

This measure returns the number of times each unique combination appears in the dataset. Similarly, Column V in Excel sums Units Sold using SUMIFS. The equivalent DAX measure is:

Sum of Related Segment =
CALCULATE(
    SUM(Sales[Units Sold]),
    FILTER(
        Sales,
        Sales[Month] = EARLIER(Sales[Month]) &&
        Sales[Customer] = EARLIER(Sales[Customer]) &&
        Sales[Related Segment] = EARLIER(Sales[Related Segment]) &&
        Sales[Product] = EARLIER(Sales[Product])
    )
)

This measure calculates the total Units Sold for each unique combination. Finally, the corrected SumFix, which is Column W = V / U, can be achieved in DAX using the DIVIDE function to handle potential division errors:

SumFix = DIVIDE([Sum of Related Segment], [Count of Related Segment], 0)

For example, in your table, the combination of January, One, Mega, and Product 1 appears twice with a total of 13 units sold and a count of 2. Therefore, the SumFix would be calculated as 13 / 2 = 6.5. This DAX approach ensures accurate aggregation by properly accounting for duplicate entries across multiple lines, providing a more dynamic and efficient solution in Power BI.

Best regards,

[JlmillerRedmond]

The solution worked great.  I need to expand on it a bit.  For each line, I need to do the same calculation for each of the immediately preceding months.  For dates, the time dimension has serial numbers for each month to eliminate the need for date math.  So the additional calculations would be currentMonth - 1, and currentMonth - 2.  I've tried adding this math to the formulas in the solution on each side of the month filter elements and both at the same time without success.

Any help on how to create theses additional measures will be greatly appreciated.

[JlmillerRedmond]

Resolved - instead of attempting to extend the COUNTROWS and SUM logic to prior month data, I used the LOOKUPVALUE function to lookup from the calculated field that uses SUM and COUNTROWS.  Works beautifully.