Forum Discussion
needs % share in row wise
Hi
My data table looks like
id status
1 a
2 a
3 a
4 a
5 a
6 a
7 b
8 b
9 c
10 c
I want to display below table in the report. Can u help me resolve this
status # Count %share
a 5 50%
b 2 20%
c 2 20%
Hi Anonymous
Try this, where Table1 is the name of the first table you show:
1. Place Table1[status] in the rows of a matrix visual
2. Create these two measures and place them in values of the matrix visual>Note the second measure uses the first one
MCount = COUNT(Table1[id])
MP%share = DIVIDE([MCount], CALCULATE([MCount], ALL(Table1)))
3. Select [MP%share] to be shown as percentage if required
1st Method
Assuming that your table name is "Table1".
create below DAX measure for the count.
#Count = CALCULATE ( COUNT ( Table1[Id] ) )
below DAX measure for the percentage.
%share = VAR c = [#Count] VAR totalcount = CALCULATE ( COUNT ( Table1[Id] ), ALL ( Table1 ) ) VAR result = ( c / totalcount ) RETURN resultchoose the table visual then add [status] column and both measures.
Change the format of "%share" as % in the modeling tab.
2nd Method
- Choose table visual then add [status] column.
- again add [status] column and change its representation as count.
- repeat 2nd step and show value as "percent of grant total"
You can rename the column headers if required.
- Anonymous7 years ago
Great!!!!!!!!!
4 Replies
- AlB
Community Champion
Hi Anonymous
Try this, where Table1 is the name of the first table you show:
1. Place Table1[status] in the rows of a matrix visual
2. Create these two measures and place them in values of the matrix visual>Note the second measure uses the first one
MCount = COUNT(Table1[id])
MP%share = DIVIDE([MCount], CALCULATE([MCount], ALL(Table1)))
3. Select [MP%share] to be shown as percentage if required
- AnonymousNot applicable
Thanks, its working
- nandukrishnavs
Community Champion
1st Method
Assuming that your table name is "Table1".
create below DAX measure for the count.
#Count = CALCULATE ( COUNT ( Table1[Id] ) )
below DAX measure for the percentage.
%share = VAR c = [#Count] VAR totalcount = CALCULATE ( COUNT ( Table1[Id] ), ALL ( Table1 ) ) VAR result = ( c / totalcount ) RETURN resultchoose the table visual then add [status] column and both measures.
Change the format of "%share" as % in the modeling tab.
2nd Method
- Choose table visual then add [status] column.
- again add [status] column and change its representation as count.
- repeat 2nd step and show value as "percent of grant total"
You can rename the column headers if required.
- AnonymousNot applicable
Great!!!!!!!!!