Calculate a measure by two categories

[sabry_303]

Hey Gurus,

I have the below table, and I  would like to create a measure to calculate % hours spent on each category per person.

So Jade will be 33% (1/3) in Category A ... etc... any ideas?

Category	Hours	Name
A	1	Jade
A	5	Adam
B	4	Adam
B	1	Adam
D	2	Jade

[TonyZhou1980 · Accepted solution]

Hi Sabry,

Please try below measures, and make a visual as below,

% of hours per name =

var _name = max(Data[Name])

var _category = max(Data[Category])

var _currenthours =

    calculate(

        sum(Data[Hours]),

        Data[Category]=_category

    )

var _hours =

    calculate(

        sum(Data[Hours]),

        Data[Name]=_name,

        all()

    )

return

    if(

        ISINSCOPE(Data[Category]),

        divide(_currenthours,_hours),

        blank()

    )

Regards

Tony

[sabry_303]

Thanks Tony a million, How to exclude ColumnX="Y" from sum of hours?

[TonyZhou1980]

Hi Sabry,

I do not quite follow you question "How to exclude ColumnX="Y" from sum of hours", please explain a bit more^-^.

Regards

Tony

[Ashish_Mathur]

Hi,

How is Jade 1/3 for category A????? 

[FreemanZ]

hi @sabry_303 

try to plot a table visual with name, category, and a measure like this:

Ratio =

VAR _name = MAX(TableName[Name])

VAR _hours = SUM(TableName[Hours])

VAR _hoursall =

SUMX(

   FILTER(

       ALL(TableName), 

       TableName[Name]=_name

   ),  

TableName[Hours])

)

RETURN

DIVIDE(_hours, _hoursall)