Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
Hi @nicole1995 ,
We can use the following steps to meet your requirement:
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]
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]))
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,
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 -
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:
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]
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]))
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,
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |