Calucate Row with Matrix

[nicole1995]

Hello

I have to create a table/matrix which has columns by month and a value that counts the number of ID's. Each row has its own logic which I have tried to show in the table example below. 

I'm not sure how to do this though. I was looking at using measures but to my very little BI knowledge measures only return one value, not a list ? So im at a bit off a loss.

I hope i have given enough detail to my issue and any help will be really appreciated. 

Row Logic	Row Name	January	rest of Months …
Count of  Subscriptions with Start Date in Month WHERE Subscription Status = Current AND Company = B AND Join Date in Month	B inceptions - number of corporate
Count of  Members with Start Date in Month WHERE Subscription Status = Current AND Company = B AND Join Date in Month	B inceptions - number of members
Sum (B Premium) + Sum (A Subscription) WHERE Start Date in Month	Incepted premium - total  (B premium and A subscription
Sum (B Subscription)	incepted premium - B premium
Sum (A Subscription)	Incepted premium - A subscription

[amitchandak]

Can this be done using measure on row?

There is a property Show on rows. That can do

[nicole1995]

I've tried creating a basic measure which one of my rows would use and I get this  -

after looking on google it suggested to use the formula but to create a table so i did that but once i add more than one table to the matrix it loses the structure i need

[v-lid-msft · Accepted solution]

Hi @nicole1995 ,

We can use the following steps to meet your requirement:

1. Create five measures that meets your row logic.

A subscription = CALCULATE(SUM(SubHeader[Subscription]),FILTER(SubHeader,SubHeader[Company]="A"))

B inceptions - number of corporate = CALCULATE(COUNT(SubHeader[Subscription]),FILTER(SubHeader,SubHeader[SubStatus]="Current"&&SubHeader[Company]="B"))

B inceptions - number of members = CALCULATE(COUNT(SubHeader[Members]),FILTER(SubHeader,SubHeader[SubStatus]="Current"&&SubHeader[Company]="B"))

B premium = CALCULATE(SUM(SubHeader[Premium]),FILTER(SubHeader,SubHeader[Company]="B"))

Incepted premium - total  (B premium and A subscription) =

[B premium] + [A subscription]

2. Then we can use Enter Date to create a new table that has one column based on the measures’ name.

3. Create a measure in new table,

Row Measure =

SUMX(

    VALUES('Table'[Row]),

    SWITCH(

        'Table'[Row],

        "A subscription",[A subscription],

        "B premium",'SubHeader'[B premium],

        "Incepted premium - total  (B premium and A subscription)",'SubHeader'[Incepted premium - total  (B premium and A subscription)],

        "B inceptions - number of corporate",'SubHeader'[B inceptions - number of corporate],

        "B inceptions - number of members",[B inceptions - number of members]))

4. At last we can put the table[row] in the Row and table[Row Measure] in the Value.

We can get the result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

Best regards,