Forum Discussion
Issue Adding Measure to Matrix Rows Field
Hello,
I am using the measure below in my report. However, when I try to add the Customer Status measure to the matrix table's Rows field, I am unable to do so. Could this be due to the way I have created the measure?
Customer Status =
IF(
[Lost Client Duration] > 3, "Lost Client",
IF(
[Months Difference] > 12, "Existing Client",
IF(
[Months Difference] > 0 && [Months Difference] <= 12, "NBTO",
"Trialing Client"
)
)
)
Lost Client Duration =
DATEDIFF(
[Last Sale Date ALL],
[Last Date in selected Month],
MONTH
)
Months Difference =
DATEDIFF(
[First Start Date ALL],
[Last Date in selected Month],
MONTH
)
Thanks
Create a disconnected table that holds the value of your expected output of your customer status measure. You can use enter data
Then create this measure which evaluates the [Customer Status] by customer in a temporary table
Status by Customer = VAR _tbl = --temporary table of status by by customer and the corresponding sales ADDCOLUMNS ( SUMMARIZE ( Customer, Customer[Customer_Code] ), "@Status", [Customer Status], "@Sales", [Sales] ) VAR _filtered_tbl = FILTER ( _tbl, [@Status] IN VALUES ( CustomerStatus[Status] ) ) VAR _sales = SUMX ( _filtered_tbl, [@Sales] ) VAR _customer = COUNTROWS ( _filtered_tbl ) RETURN _customer --use _sales if you want to re turn the sales by customer status
4 Replies
Measures don't have row context so you cannot use them as dimensions. They are evaluated based on the other fields added to a viz. If you want that be shown in the rows field, a placeholder row must exist in the same field as well. For example, if a row field contains A, B, C but doesn't have D but you want D to appear, D must exist in that row field and then write measure to return the desired value if the current row is D.
If you could provide a workable sample data (not an image) and your expected result from that with the reasoning behind, we might be able to whip up a working solution for you.
Hi danextian - Please find attached the sample file. Click Here
Also the result is to be displayed as below.
Customer Status Count of Customers Sales Existing Lost NBTO Thank You!
Create a disconnected table that holds the value of your expected output of your customer status measure. You can use enter data
Then create this measure which evaluates the [Customer Status] by customer in a temporary table
Status by Customer = VAR _tbl = --temporary table of status by by customer and the corresponding sales ADDCOLUMNS ( SUMMARIZE ( Customer, Customer[Customer_Code] ), "@Status", [Customer Status], "@Sales", [Sales] ) VAR _filtered_tbl = FILTER ( _tbl, [@Status] IN VALUES ( CustomerStatus[Status] ) ) VAR _sales = SUMX ( _filtered_tbl, [@Sales] ) VAR _customer = COUNTROWS ( _filtered_tbl ) RETURN _customer --use _sales if you want to re turn the sales by customer status
