calculate the percentage size of partial subset DAX

[MaleneL]

Hi all 
I am working with at dataset, where we need to calculate a Report Measuer that calculate the percentage size for only some of the category, en excel it would look like this (we do not want 3. privat to be part of the procentage for the rest):

here is some data that can be used as an excempel

FT_branche_niv2	Amount
2.1 Landbrug, jagt og skovbrug og fiskeri	182076394
2.2 Industri og råstofindvinding	18784097
2.3 Energiforsyning	10081600
2.4.1 Gennemførelse af byggeprojekter	2878331
2.4.2 Opførelse af bygninger	2896211
2.4.3 Bygge og anlæg i øvrigt	9469046
2.5 Handel	23195879
2.6.1 Transport, post- og kurertjenester	8747250
2.6.2 Hoteller og restauranter	5572916
2.7 Information og kommunikation	13103419
2.8 Finansiering og forsikring	30235439
2.9.2 Udlejning mv. af fast ejendom	45626339
2.9.3 Ejendomsmæglere og ejendomsadministration	4458115
2.10 Administrative og support services	5838250
2.10 Andre serviceydelser	844451
2.10 Børnehaver	23566507
2.10 Foreninger og organisationer	39182434
2.10 Kultur og Fritid	29687015
2.10 Offentlig forvaltning og forsvar	5555
2.10 Prof, videnskabelig og tekniske services	41265004
2.10 Sociale Institutioner	77646136
2.10 Sundhedsvæsen	11812738
2.10 Undervisning	146340663
3. Privat	1313260033

Any suggestion?

[v-yiruan-msft]

Hi @MaleneL ,

I created a sample pbix file(see the attachment),please check if that is what you want. You can create two calculated column as below to get it:

Category Set = 
VAR _posi =
    IFERROR ( SEARCH ( ".", 'Table'[Category], 1, 0 ), 0 )
RETURN
    LEFT ( 'Table'[Category], _posi - 1 )

% in category = 
DIVIDE (
    [Sum of Amount],
    CALCULATE (
        SUM ( 'Table'[Sum of Amount] ),
        FILTER ( 'Table', 'Table'[Category Set] = EARLIER ( 'Table'[Category Set] ) )
    )
)

Best Regards

[MaleneL]

Hi Rena

thanks for your solution. 

I can see that I have not been correct in exact enough. I need it as at Report Measure and not part of my dataset as you have shown 
But thanks for your help 🙏

[v-yiruan-msft · Accepted solution]

Hi @MaleneL ,

I updated the sample pbix file(see the attachment), please find the details in it.

Measure = 
VAR _tab =
    ADDCOLUMNS (
        'Table',
        "@categoryset",
            VAR _posi =
                IFERROR ( SEARCH ( ".", 'Table'[Category], 1, 0 ), 0 )
            RETURN
                LEFT ( 'Table'[Category], _posi - 1 )
    )
RETURN
    DIVIDE (
        SUM ( 'Table'[Sum of Amount] ),
        CALCULATE (
            SUM ( 'Table'[Sum of Amount] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                LEFT (
                    'Table'[Category],
                    IFERROR ( SEARCH ( ".", 'Table'[Category], 1, 0 ), 0 ) - 1
                )
                    = MAXX ( _tab, [@categoryset] )
            )
        )
    )

Best Regards

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...