Calculate frequency by sample type

[fpennisi17]

Hi,

I have a table similar to the following one:

Asset	State	Type	Samples
AAAAA	0	X	1000
AAAAA	1	X	200
AAAAA	2	X	300
AAAAA	3	X	50
AAAAA	0	Y	500
AAAAA	1	Y	130
AAAAA	2	Y	180
AAAAA	3	Y	25

The only value I have is the number of samples for a given pair of State and Type.

I would like to diplay the data on a matrix visual, having States as rows and Type as columns.
I want to have 2 values for each combination, the first one being the number of samples, while the second one the percentage of samples of that type calculated over the total samples of the same type (beside of the state).

With the given sample table, for the state 0 row I should obtain:

Samples for type X = 1000

Percentage for type X = 1000/(1000+200+300+50) = 0,645 = 64.5%

Samples for type Y = 1000

Percentage for type Y = 500/(500+130+180+25) = 0,599 = 59.9%

I'm sure this should be pretty straightforward with DAX, but I'm facing more difficulties than I thought.

Can someone suggest me how to obtain this result?

Thank you.

[Fowmy]

@fpennisi17 

Create two measure and add to a matrix;

Count = SUM(Table1[Samples])
----------------------------------------------------
Percent = 
DIVIDE(
    [Count],
    CALCULATE(
        [Count],
        ALLSELECTED(Table1[State])
    )
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[CNENFRNL · Accepted solution]

Hi, @fpennisi17 , you might want to try this measure,

% Type =
DIVIDE (
    MAX ( 'Table'[Samples] ),
    CALCULATE ( SUM ( 'Table'[Samples] ), ALLEXCEPT ( 'Table', 'Table'[Type] ) )
)

[fpennisi17]

Great!

Thank you!