I have a sales fact table like this :
sales sample fact table
| IdCustomer | OrderKey | Product | product code | Brand |
|---|---|---|---|---|
| 25 | 12256 | product_x | 514545 | brand1 |
| 25 | 12256 | product_y | 514546 | brand2 |
| 26 | 13254 | product_z | 514541 | brand3 |
| 27 | 13242 | product_y | 514546 | brand2 |
| 25 | 15529 | product_x | 514545 | brand1 |
| 25 | 15529 | product_h | 514549 | brand4 |
| 25 | 15529 | product_y | 514546 | brand2 |
| 27 | 17546 | product_h | 514549 | brand4 |
| 27 | 17546 | product_g | 514544 | brand5 |
| 25 | 12250 | product_x | 514545 | brand1 |
| 27 | 12349 | product_y | 514546 | brand2 |
| 25 | 12506 | product_z | 514541 | brand3 |
the result i want is :
result pivot table
I want to calculate the Total Customers Orders , the total count of all orders by customers, who buy that brand ( in the filter context) at least one time. Not the total orders by the customer for only that brand ( column Orders)
I write this measure : it’s wrong in the grand total.
CALCULATE(SUMX(SUMMARIZE(Sales,Sales[OrdersKey]),1),ALL(Dim_Brand[Brand])
can anyone help?
Thanks,
I write this measure : it’s wrong in the grand total( due to the ALL function)
CALCULATE(SUMX(SUMMARIZE(Sales,Sales[OrdersKey]),1),ALL(Dim_Brand[Brand])
New contributor
robredo_9 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
Try the following Measure:
Total Customer Orders =
var customers = DISTINCT(Sales[IdCustomer])
return
CALCULATE(
DISTINCTCOUNTNOBLANK(Sales[OrderKey]),
REMOVEFILTERS(Dim_Brand[Brand]),
Sales[IdCustomer] IN customers
)
maybe you can try this
MEASURE =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Brand] ),
ALL ( 'Table' ),
'Table'[IdCustomer] = MAX ( 'Table'[IdCustomer] )
)
Measure 2 = sumx(VALUES('Table'[IdCustomer]),[measure])