Adding Percentage column to matrix using the subtotals from that matrix

[JonLow]

Realtively new to PBI so I'm sorry if I'm missing something obvuous here.

Please see attachment. What seems like a simple thing has csaused me a lot of consternation today. You can see a straight forward Matrix below and all I'd like to do is put percentage column next to the Count of Org column that uses the calculated sub total from the matrix. So AML,CT day 1*612 and so on and so forth down the list. 

I tried a few things from posts that I've seen here but it doesn't work for me. Any help would be greatly appreciated.

Edit: image added, thanks all.

[v-angzheng-msft]

Hi, @JonLow 

Unfortunately, no attachment was found.
Please refer to this blog for samples😉

How to provide sample data in the Power BI Forum

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

[JonLow]

Wel spotted, I've added the image of my tables. Thanks

[v-angzheng-msft]

Hi , @JonLow 

Do you mean to add a column next to the Count of Org to show the percentage of the count, like the following?

Please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate solution.

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

[JonLow]

Thanks for having another look. That is exaclty what I'm looking for. See attachment re sample data.

Test Data 

[v-angzheng-msft]

Hi, @JonLow 

Try to create a measure like this:

Percentage% =
VAR _subTotal =
    CALCULATE (
        COUNT ( Sheet1[Org] ),
        FILTER ( ALL ( 'Sheet1' ), 'Sheet1'[Modality] = MAX ( 'Sheet1'[Modality] ) )
    )
VAR _currentCount =
    CALCULATE (
        COUNT ( Sheet1[Org] ),
        FILTER ( 'Sheet1', 'Sheet1'[Modality] = MAX ( 'Sheet1'[Modality] ) )
    )
VAR _if =
    IF ( ISINSCOPE ( Sheet1[Modality] ), DIVIDE ( _currentCount, _subTotal ), 1 )
RETURN
    _if

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JonLow]

Hi, Thanks again for having a look at this for me and I thought you had it there. However, when you add the slicer in for the dates the calculations don't quite work again. Please see screen attached below. 

[v-angzheng-msft]

Hi, @JonLow 

If the slicer needs to be used, then simply change the All function above to the ALLSELECTED function.

Percentage% = 
//
var _count=COUNT(Sheet1[Org])
var _subTotal=CALCULATE(COUNT(Sheet1[Org]),FILTER(ALLSELECTED('Sheet1'),'Sheet1'[Modality]=MAX('Sheet1'[Modality])))
var _subTotal_2 =IF(ISINSCOPE(Sheet1[Modality]),_subTotal,_count)
//

var _currentCount=CALCULATE(COUNT(Sheet1[Org]),FILTER('Sheet1','Sheet1'[Modality]=MAX('Sheet1'[Modality])))
var _if=IF(ISINSCOPE(Sheet1[Modality]),DIVIDE(_currentCount,_subTotal_2),1)
return _if

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JonLow]

Hi, Thanks again for the update and that works for that amount of data. But I have a lot more. When I add more orgs the subtotals are no longer right. I can see the subtotal calculation is grouping the differnt bands of modailities together from the different orgs. Is there anyway to keep it in the same org? See attched screen shot and data for example.

Updated Data 

[v-angzheng-msft · Accepted solution]

Hi, @JonLow 

update:

2_Percentage% = 

VAR _mondality =CALCULATE (COUNT ('Sheet2'[Org]),FILTER (ALLSELECTED ( 'Sheet2' ),'Sheet2'[Org]=MAX('Sheet2'[Org])&&'Sheet2'[Modality] = MAX ( 'Sheet2'[Modality] )))
VAR _org =CALCULATE (COUNT ('Sheet2'[Org]),FILTER (ALLSELECTED ( 'Sheet2' ),'Sheet2'[Org]=MAX('Sheet2'[Org])))
VAR _count =CALCULATE (COUNT ('Sheet2'[Org]),ALLSELECTED(Sheet2))
VAR _2_subTotal = IF (ISINSCOPE ( Sheet2[Org] ),IF (ISINSCOPE ( Sheet2[Modality] ),_mondality,_org),_count)
//
var _currentCount=COUNT('Sheet2'[Org])
return DIVIDE(_currentCount,_2_subTotal)

2_subTotal = 
VAR _mondality =CALCULATE (COUNT ('Sheet2'[Org]),FILTER (ALLSELECTED ( 'Sheet2' ),'Sheet2'[Org]=MAX('Sheet2'[Org])&&'Sheet2'[Modality] = MAX ( 'Sheet2'[Modality] )))
VAR _org =CALCULATE (COUNT ('Sheet2'[Org]),FILTER (ALLSELECTED ( 'Sheet2' ),'Sheet2'[Org]=MAX('Sheet2'[Org])))
VAR _count =CALCULATE (COUNT ('Sheet2'[Org]),ALLSELECTED(Sheet2))
RETURN
    IF (
        ISINSCOPE ( Sheet2[Org] ),
        IF (
            ISINSCOPE ( Sheet2[Modality] ),
            _mondality,
            _org
        ),
        _count
    )

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JonLow]

Thanks that's perfect, appreciate the help with this.

[lbendlin]

Disclaimer:  This is some rather heavy stuff when you are new to Power BI.

Creating a “custom” or “hybrid” matrix in PowerBI - Microsoft Power BI Community

[JonLow]

Thanks for this, it does seem complicated. I've updated my post with a little more info (images of data). Not sure if I explained the problem too well but I was trying to just add a measure in to the values part of the matrix to calculate the percenatage but I can't figure out how to reference the total of each combination of org and modailty. Hope that make more sense.