Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nicole1995
Frequent Visitor

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

 

 

35.png

 

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


37.png

 

  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,

 

38.png

 

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

Can this be done using measure on row?

There is a property Show on rows. That can do

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

Untitled.png

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

 

 
 

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]

 

 

35.png

 

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


37.png

 

  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,

 

38.png

 

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.