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. 

 

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 LogicRow NameJanuary 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 MonthIncepted premium - total  (B premium and A subscription  
Sum (B Subscription) incepted premium - B premium  
Sum (A Subscription) Incepted premium - A subscription   
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,

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Can this be done using measure on row?

    There is a property Show on rows. That can do

    • 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
        Icon for Community Support rankCommunity 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,