Get ALL sales per customer on product level - Hierarchy

[Anonymous]

Hey all,

I have a problem when trying to sum up the total customer sales on product level.

In this case, I would usually write a measure like:

SALES ALL =
CALCULATE(
[SALES],
ALL(PART_NO))

But, the issue is that this will return ALL parts under the customer, not only the part that the customer have bought.
So in the case where I have 10.000 different parts in table 'PARTS',
I would get ALL parts under the customer-product-matrix.

So I add to the measure (in bold) a filter that would exclude the parts where sales > 0:

SALES ALL =
CALCULATE(
[SALES],
FILTER(
'PARTS', [SALES] > 0),
ALL(PART_NO))

But that returns exactly the same as SALES does:

CUSTOMER | PART_NO | SALES | SALES ALL
ABC PART1 320e 320e
ABC PART2 220e 220e
ABC PART3 150e 150e
ABC PART4 120e 120e
CDE PART1 400e 400e
CDE PART2 300e 300e

When my desired result is:

CUSTOMER | PART_NO | SALES | SALES ALL
ABC PART1 320e 810e
ABC PART2 220e 810e
ABC PART3 150e 810e
ABC PART4 120e 810e
CDE PART1 400e 700e
CDE PART2 300e 700e

I tried the gurus: https://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/#

But they've a different method since it's the same table.

The tables are structured as following:

'CUSTOMERS' -->-- 'ORDERLINES' --<-- 'PARTS'

And it need to be a measure, since I have date filters on sales etc.

Any help from you guys?

Thanks in advance!

[Fowmy]

@Anonymous 

Try this way:

SALES ALL =
CALCULATE(
    [SALES],
    ALLSELECTED(PART_NO)
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

@Fowmy 

This I have tried, and although it gives the correct values for the parts that has sales > 0, but it also includes all the 10.000 rows with no sales:

But that returns exactly the same as SALES does:

CUSTOMER | PART_NO | SALES | SALES ALL
ABC PART1 320e 810e
ABC PART2 220e 810e
ABC PART3 150e 810e
ABC PART4 120e 810e

ABC PART5 0e     810e

ABC PART6 0e     810e

ABC PART7 0e     810e

... 10 thousand rows until PART 10.000
CDE PART1 400e 700e
CDE PART2 300e 700e

So I don't see it as an acceptable solution. Of course it would be possible to make a visual level filter where sales > 0, but I find it not as safe, plus the measure takes a whole lot of processing power of going through 10k rows per each customer.

[Anonymous · Accepted solution]

SALES ALL = 

IF(

     isblank([Sales]),

       blank(),

     CALCULATE(

     [Sales],

     ALLNOBLANKROW(PBI_INVENTORY_PART[PART_NO])))

An if function seemed to do the work also. Thanks for the help!

[Fowmy]

@Anonymous 

You may have enabled show Items with no data on the Customer and Part, try turning them off

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

@Fowmy 

The show items with no data was turned off,

but I didn't know it existed, so thanks for that.

Any comments regarding the IF-funtion solution? Would it be against the general rule of writing DAX (I guess)? 🙂

[amitchandak]

@Anonymous , I am not clear on the need of all(Part)

Try a measure like

sumx(filter(summarize(Table, Customer[customer], part[part], "_1", [sales]),[sales]>0),[_1])

[Anonymous]

@amitchandak Thanks, but this give the following:

It returns exactly the same as SALES does:

CUSTOMER | PART_NO | SALES | SALES ALL
ABC PART1 320e 320e
ABC PART2 220e 220e
ABC PART3 150e 150e
ABC PART4 120e 120e
CDE PART1 400e 400e
CDE PART2 300e 300e