Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Community,
So I have this database with about 2 million rows structured like that.
So what I want to do is show them in a matrix like this.
I was trying to add the word "Total" as another category and calculate the values with a new measure.
This in order to add that new measure as the "Values" for the matrix, but I still did not get any success.
Could you help me with some ideas so I can do it?
Solved! Go to Solution.
Hi @sneidercub ,
I've updated the Sum with Total measure to:
Sum with Total =
VAR __One =
SELECTEDVALUE ( Category[Category] )
RETURN
IF (
__One = "Total"
|| NOT ( HASONEVALUE ( Category[Category] ) ),
CALCULATE ( SUM ( 'Table'[Value] ), ALL ( 'Table'[Category] ) ),
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER ( 'Table', 'Table'[Category] = __One )
)
)
The rest involves formatting the matrix visual. However, selectively removing the subtotal for each item in a column is not currently supported.
You may refer to the same link for the updated pbix.
Proud to be a Super User!
Hi @sneidercub ,
My approach would be to create a disconnected table (no relationship with other tables) and use that to hold the values needed.
Calculated table:
Category =
VAR __T1 =
DISTINCT ( 'Table'[Category] )
RETURN
UNION ( __T1, ROW ( "Category", "Total" ) )
Measure:
Sum with Total =
VAR __CATEGORY =
SELECTEDVALUE ( Category[Category] )
RETURN
IF (
__CATEGORY = "Total",
CALCULATE ( SUM ( 'Table'[Value] ), ALL ( 'Table'[Category] ) ),
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER ( 'Table', 'Table'[Category] = __CATEGORY )
)
)
Output:
Sample PBIX: https://drive.google.com/file/d/1Rnlav71Z0idt6lVRmyI70kjI6JgBLrqe/view?usp=sharing
Proud to be a Super User!
That's great!
Thank you for your help!
I think I may have simplified too much the issue, let me try to show a little more about what I'm working on:
so this is a "better" view for the data base:
besides year there are like other 4 or 5 "categories" that I would have to take in consideration, and the final view I want to get should look more like this:
and taking into consideration it's a 2 million rows database I'm not sure if creating a new disconnected table with a cross join will help.
Could you help me with your approach to this?
Thank you again @danextian
Please post a sample data that can be easily copy-pasted.
Proud to be a Super User!
Here you are:
https://docs.google.com/spreadsheets/d/14CSGPTs_FFL3DWlEckaXKCWjHF-egpBt/edit?usp=sharing&ouid=11673...
Thank you so much!
Hi @sneidercub ,
I've updated the Sum with Total measure to:
Sum with Total =
VAR __One =
SELECTEDVALUE ( Category[Category] )
RETURN
IF (
__One = "Total"
|| NOT ( HASONEVALUE ( Category[Category] ) ),
CALCULATE ( SUM ( 'Table'[Value] ), ALL ( 'Table'[Category] ) ),
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER ( 'Table', 'Table'[Category] = __One )
)
)
The rest involves formatting the matrix visual. However, selectively removing the subtotal for each item in a column is not currently supported.
You may refer to the same link for the updated pbix.
Proud to be a Super User!
Amazing! That's what I was looking for. Thank you so much!
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
111 | |
96 | |
96 | |
67 | |
63 |