Creating a matrix with rounded numbers but exact total + a "difference" row/column

[casperholtfarve]

Hi everyone

I have been trying to figure this one out for a very loooooong time now and I am stuck.
So now I really hope, that someone in this great community might be able to help me out.

My desired end result:
I want to be able to create a matrix like this in Power BI:

The reason for the red text will be explained shortly.

I have some data for a different category for each year. This data is simply being generated by a simple SUM()-function of the data.

The issue for me is, that we are not allowed to show specific/small numbers, so in order to mask this we are rounding everything to the nearest 5. By usind MROUND(measure, 5).

That's great at that would generate this matrix:

But what I really want to do is use the rounded numbers (in red above) but the ACTUAL/CORRECT totals (in red below😞

And on top of that, I would love to be able to actually show another row/column with the difference.
Hence we could tell the user, that there is a difference in the overall total of +2 or -2 or whatever. But not show the user which specific cell(s) the difference are at. And therefor they do not know the exact difference by cell level. So in the end my desired goal matrix is this:

My datamodel for this simple examply would be something like this:

Please feel free to ask any questions, if I am not clear enough.
Thanks a lot in advance!

IF the difference part is not possible. Please also advice in how to create the matrix without the difference row/column but with the actual total and the rounded numbers in the different cell values.

[casperholtfarve]

@PaulDBrown I am looking at your post: https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1... 

And was wondering if my current issue (explained in the forum post) could be solved by your ideas? I am having a hard time getting my head around how it could work for my specific data/model? Can you suggest a solution?

Thanks alot in advance.

Br
Casper.

[PaulDBrown]

Any chance you can provide sample data or a link to a sample PBIX file?

[casperholtfarve]

Hi @PaulDBrown 
Than you so much for responding.

Yea sure give me 15 minutes and I will create a PBIX report for you to play with!

[casperholtfarve]

Hi @PaulDBrown 
Sorry for the delay. It was a big data sample I needed to query from SQL.
Don't mind the different names and columns, most are not used in this specific case but it was easier to include everything rather than deselecting and hiding, since I wasnt sure what connected through what.

Samle Solution.pbix

The upper most important thing is that I use the measure "Number of Incidents Rounded" for all cell values but I would like the measure "Number of Incidents" for the totals. Both column and row totals.

If possible a "Difference" column/row would be nice as well. But this is less important.

Thanks in advance. 
Casper. 

[PaulDBrown]

See if this works. To enable a "Difference" Column and Row, I set up new tables for Category and Year following this pattern:

Matrix Cats =
VAR _CatNumber =
    DISTINCTCOUNT ( Categories[BRANCHEGRP_DB07_CONCAT_1] )
VAR _CatList =
    ADDCOLUMNS (
        VALUES ( Categories[BRANCHEGRP_DB07_CONCAT_1] ),
        "Order",
            IF (
                ISERROR ( VALUE ( LEFT ( Categories[BRANCHEGRP_DB07_CONCAT_1], 2 ) ) ) = TRUE (),
                _CatNumber,
                VALUE ( LEFT ( Categories[BRANCHEGRP_DB07_CONCAT_1], 2 ) )
            )
    )
VAR _Other = {
    ( "Total", _CatNumber * 10 ),
    ( "Difference", _CatNumber * 100 )
}
RETURN
    UNION ( _CatList, _Other )

Then I set up the relationships in the model

You then set up the Matrix with the fields from these new tables and the following measure:

Measure for Matrix =
    VAR _YearTotal =
        CALCULATE ( [Number of Incidents], ALLSELECTED ( 'Matrix Year' ) )
    VAR _CatTotal =
        CALCULATE ( [Number of Incidents], ALLSELECTED ( 'Matrix Cats' ) )
    VAR _YTRounded =
        CALCULATE ( [Number of Incidents Rounded], ALLSELECTED ( 'Matrix Year' ) )
    VAR _CTRounded =
        CALCULATE ( [Number of Incidents Rounded], ALLSELECTED ( 'Matrix Cats' ) )
    VAR _DifferenceYear = _YearTotal - _YTRounded
    VAR _DifferenceCat = _CatTotal - _CTRounded
    VAR _GrandTotal =
        CALCULATE (
            [Number of Incidents],
            ALLSELECTED ( 'Matrix Cats' ),
            ALLSELECTED ( 'Matrix Year' )
        )
    VAR _GTRounded =
        CALCULATE (
            [Number of Incidents Rounded],
            ALLSELECTED ( 'Matrix Cats' ),
            ALLSELECTED ( 'Matrix Year' )
        )
    RETURN
        SWITCH (
            TRUE (),
            AND (
                SELECTEDVALUE ( 'Matrix Cats'[BRANCHEGRP_DB07_CONCAT_1] ) = "Total",
                SELECTEDVALUE ( 'Matrix Year'[AARSTAL] ) = "Total"
            ), _GrandTotal,
            AND (
                SELECTEDVALUE ( 'Matrix Cats'[BRANCHEGRP_DB07_CONCAT_1] ) = "Difference",
                SELECTEDVALUE ( 'Matrix Year'[AARSTAL] ) = "Difference"
            ), BLANK (),
            AND (
                SELECTEDVALUE ( 'Matrix Cats'[BRANCHEGRP_DB07_CONCAT_1] ) = "Difference",
                SELECTEDVALUE ( 'Matrix Year'[AARSTAL] ) = "Total"
            ),
                CALCULATE (
                    SUMX (
                        VALUES ( 'Date'[AARSTAL] ),
                        CALCULATE ( [Number of Incidents], ALLSELECTED ( 'Matrix Cats' ) )
                            - CALCULATE ( [Number of Incidents Rounded], ALLSELECTED ( 'Matrix Cats' ) )
                    ),
                    ALLSELECTED ( 'Matrix Year' )
                ),
            AND (
                SELECTEDVALUE ( 'Matrix Cats'[BRANCHEGRP_DB07_CONCAT_1] ) = "Total",
                SELECTEDVALUE ( 'Matrix Year'[AARSTAL] ) = "Difference"
            ),
                CALCULATE (
                    SUMX (
                        VALUES ( 'Categories'[BRANCHEGRP_DB07_CONCAT_1] ),
                        CALCULATE ( [Number of Incidents], ALLSELECTED ( 'Matrix Year' ) )
                            - CALCULATE ( [Number of Incidents Rounded], ALLSELECTED ( 'Matrix Year' ) )
                    ),
                    ALLSELECTED ( 'Matrix Cats' )
                ),
            SELECTEDVALUE ( 'Matrix Year'[AARSTAL] ) = "Total", _YearTotal,
            SELECTEDVALUE ( 'Matrix Year'[AARSTAL] ) = "Difference", _DifferenceYear,
            SELECTEDVALUE ( 'Matrix Cats'[BRANCHEGRP_DB07_CONCAT_1] ) = "Total", _CatTotal,
            SELECTEDVALUE ( 'Matrix Cats'[BRANCHEGRP_DB07_CONCAT_1] ) = "Difference", _DifferenceCat,
            [Number of Incidents Rounded]
        )

To get:

Sample PBIX file attached

[casperholtfarve]

WOW!! 
@PaulDBrown Thank you so much. I only miss one last thing, for this to solve (I believe) our entire problematics.

Let's say that we have a hierarchy instead of just 1 category. How would you set up the tables for this?

I have created a revised version of the PBIX. Instead of categories I have now made the visual with a new table (Geography) which consists of both regions and communes. The total and difference columns seems to work just fine! 

But now I need a new table for the total and difference rows. A table connected to the geography table i suspect. But how should this be set up, now when we use a hierarchy? 

Thank you so much in advance. 
Really appriciate this!

Samle Solution - With Hierarchy.pbix 

[PaulDBrown]

So at what levels would you want the rounded values, the real values and the difference?

[casperholtfarve]

Hi@PaulDBrown 
I am not quite sure I understand your questions fully. But if I did understand it correctly, what i preferably would like is:

- Both levels of the hierarchi should only use rounded numbers.
- Both levels of the hierarchi should hos real numbers for the total.
- Both levels of the hierarchi should have a difference column.

It seems to be working fine both when folded out and not, but I am missing the bottom 2 rows:

[casperholtfarve]

Hi @PaulDBrown 
Another issue I have stumbled upon is that when I try to use another column value from the "Categories" table. It shows correct values, but it does not hide the rows that are no longer in scope. Is this possible? So that we only show the rows with actual numbers instead of showing a bunch of zeros for all those rows making it more dynamic:

[casperholtfarve]

@PaulDBrown This I believe I could fix with a filter on the visual hiding all blank values. 
So never mind this. I only have the questions related to the hierarchies left 😄 
And how to create calculated tables/measure for instances when whe use a hierarchy rather than a single column value.

Thanks in advance.

/Casper.

[casperholtfarve]

@Greg_Deckler 
I see that @amitchandak is refererring to your blog post, but I am having trouble trying to use your method for my requirements. Is it possible for you to look at my case here and give an example on how to fix it? 

Maybe not the extra row (if not possible), but at least the extra columns then?
And if the extra row is possible, that would be amazing of course! 🙂

Thanks in advance.

[casperholtfarve]

I am using SSAS and here it seems like ISINSCOPE is not available either.
Does not seem to work by using the solution suggested by Greg. I am looking into the blog post from PaulDBrown now.

[amitchandak]

@casperholtfarve , seem like you are looking for a Hybrid display with Matrix Column and measure
https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1...
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...

vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc

[casperholtfarve]

@amitchandak yes, but not quite sute how to incoorporate this to the data that I have here. It's not really clear on those 2 forum posts, so if someone could explain it based on my data, so it would be clear, I would love that.

/Casper