Percentage Calculation for selected rows based on String

[Jasmine12]

Hi All,

I am new to Power BI. I have a table as below. I need to calculate % for only the last 3 rows and only for YTD. 

	QTD	YTD	%
Total Revenue	200	1500
US Revenue	20
UK Revenue	30
Others	50
Below SLA	100	500	Below SLA/Total Revenue
Above SLA	20	200	Above SLA/Total Revenue
Equal SLA	80	800	Equal SLA/Total Revenue

[lbendlin]

Please show the expected outcome based on the sample data you provided.

[Jasmine12]

Thanks for your reply @lbendlin . I have added the expected % column. My other issue is that both QTD & YTD are in the same column. And when I try to add any measure to this matrix visual, it adds 2 columns, 1 for QTD & 1 for YTD. I just want to QTD (the expected %)

	QTD	YTD	Expected %
Total Revenue	200	1500	100%
US Revenue	20
UK Revenue	30
Others	50
Below SLA	100	500	33%
Above SLA	20	200	13%
Equal SLA	80	800	53%

[v-junyant-msft · Accepted solution]

Hi @Jasmine12 ,

Thanks for all the replies!
And @Jasmine12 ,

when I try to add any measure to this matrix visual, it adds 2 columns, 1 for QTD & 1 for YTD

Because your QTD and YTD are both in one column, if you directly use the fields in the original table to create this matrix, then the above situation is bound to happen. This is a problem with your data structure and is by design.
If you want to add a percentage column only for YTD, I suggest you create a measure for QTD and YTD to calculate the corresponding values ​​and put them in the matrix.

Here is my sample data, I don't know whether your Total Revenue already exists in your table or you calculated it later, so I didn't put it in my test data here:

First use these two DAXs to create two measures:

QTD = 
IF (
    ISFILTERED ( 'Table'[Name] ),
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Type] = "QTD" ),
    CALCULATE (
        SUM ( 'Table'[Value] ),
        'Table'[Type] = "QTD"
            && 'Table'[Name] IN { "Below SLA", "Above SLA", "Equal SLA" }
    )
)

YTD = 
CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Type] = "YTD" )

Then use this DAX to create another measure:

Expected % = 
VAR _YTD =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Type] = "YTD" )
VAR _TotalYTD =
    CALCULATE ( SUM ( 'Table'[Value] ), ALL ( 'Table' ), 'Table'[Type] = "YTD" )
RETURN
    _YTD / _TotalYTD

Please note that if you want to add a column for YTD but not QTD, you must not put any fields in the Columns of your matrix.

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

[Ashish_Mathur]

Hi,

I'd like to try.  Share the download link of the PBI file.