Forum Discussion
Calculate frequency by sample type
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.
Hi, fpennisi17 , you might want to try this measure,
% Type = DIVIDE ( MAX ( 'Table'[Samples] ), CALCULATE ( SUM ( 'Table'[Samples] ), ALLEXCEPT ( 'Table', 'Table'[Type] ) ) )
3 Replies
Hi, fpennisi17 , you might want to try this measure,
% Type = DIVIDE ( MAX ( 'Table'[Samples] ), CALCULATE ( SUM ( 'Table'[Samples] ), ALLEXCEPT ( 'Table', 'Table'[Type] ) ) )Great!
Thank you!
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 🙂
