Forum Discussion
Customer increase sale with Dynamic Segmentation
I want to categorize the "Increase Sale" column into the following categories, but the total for "Increase Sale" is not displaying correctly with the category name. If you look at the table above, Customer_no CU323143 has an Increase Sale of $155,369.31, so it should be displayed under the category of $100k-250k.
Following are my measures:
Increase Sale =
CALCULATE (
[CY Sale] - [LY Sale],
FILTER (
VALUES ( invoices_details[customer_no] ),
[CY Sale] > 0
&& [LY Sale] > 0
&& [CY Sale] > [LY Sale]
)
)
WHERE
CY Sale =
CALCULATE (
SUM ( invoices_details[sale_cad] ),
FILTER (
VALUES ( invoices_details[customer_no] ),
COUNTROWS (
FILTER (
'category',
invoices_details[sale_cad] >= 'category'[category min value]
&& invoices_details[sale_cad] < 'category'[category max value]
)
) > 0
)
)
LY Sale =
CALCULATE([CY Sale],SAMEPERIODLASTYEAR('Calendar'[Date]))
Abrar786 , Try like
Increase Sale =
Sumx (FILTER (
VALUES ( invoices_details[customer_no] ),
[CY Sale] > 0
&& [LY Sale] > 0
&& [CY Sale] > [LY Sale]
),
[CY Sale] - [LY Sale]
)or
Sumx (FILTER (
VALUES ( invoices_details[customer_no] ),
[CY Sale] > 0
&& [LY Sale] > 0
&& [CY Sale] > [LY Sale]
),
calculate([CY Sale] - [LY Sale])
)
2 Replies
- amitchandak
Super User
Abrar786 , Try like
Increase Sale =
Sumx (FILTER (
VALUES ( invoices_details[customer_no] ),
[CY Sale] > 0
&& [LY Sale] > 0
&& [CY Sale] > [LY Sale]
),
[CY Sale] - [LY Sale]
)or
Sumx (FILTER (
VALUES ( invoices_details[customer_no] ),
[CY Sale] > 0
&& [LY Sale] > 0
&& [CY Sale] > [LY Sale]
),
calculate([CY Sale] - [LY Sale])
) - Abrar786
Helper I
Hi amitchandak ,
With my measure
Increase Sale =
CALCULATE (
[CY Sale] - [LY Sale],
FILTER (
VALUES ( invoices_details[customer_no] ),
[CY Sale] > 0
&& [LY Sale] > 0
&& [CY Sale] > [LY Sale]
)
)
I am obtaining the correct result for each customer, but I am unable to place the sum of the increase in sales under the respective category name.
Thank you.