Forum Discussion

nicole1995's avatar
nicole1995
Frequent Visitor
6 years ago
Solved

Calucate Row with Matrix

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.    ...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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]

     

     

     

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

     

    1. 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]))

     

    1. 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,

     

nicole1995's avatar
nicole1995
Frequent Visitor
6 years ago

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's avatar
v-lid-msft
Community Support
6 years ago

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]

 

 

 

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

 

  1. 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]))

 

  1. 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,