Forum Discussion
Display specific data in matrix
Hi Razwan ,
Would you please try to use the following measure:
# Lost = COUNTX ( FILTER ( SUMMARIZE ( FactTable, FactTable[Customer Name], 'Calendar'[Year], "_Total", [Total], "_TotalLY", [Total Ly] ), [Total] = 0 && [Total LY] <> 0 ), FactTable[Customer Name] )If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hello, Jihwan_Kim
Unfortunatelly I have no permissions to attach any file.
And to answer your question, it doesn't matter which year you select. By default it should count for all the years, then filter based on your selected year.
For example:
Given this hierarchy:
Customer X:
---- Division A
---- SubDiv A1
---- Division B
---- SubDiv B1
---- SubDiv B2
On top level (Customer X) has sales for 2018, 2019 and 2021.
So in a matrix/table for Customer X in:
- 2018: it will show new (logically because it's the first sales year);
- 2019: it will show nothing because it has sales for current year and previous year;
- 2020: it will show lost (because it has sales for previous year but not for current year);
- 2021: it will show new (because it has sales for current year but not previous);
All this information should be shown in a matrix/table respecting that logic + hierarchy.
Also, count the number of the customers at the top level.
The request is really weird and I am aware of it, and my head is dizzy because I am not able to solve it....
Hi Razwan ,
Would you please try to use the following measure:
# Lost =
COUNTX (
FILTER (
SUMMARIZE (
FactTable,
FactTable[Customer Name],
'Calendar'[Year],
"_Total", [Total],
"_TotalLY", [Total Ly]
),
[Total] = 0
&& [Total LY] <> 0
),
FactTable[Customer Name]
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai