Hello Friends I need help with distinct total.
Below is table
column1 Column2
A 10
B 20
A 10
C 30
the desired output is
Coulmn2
10
20
30
------
Total= 10
*total should be 10 as I want to sum only distinct values and want to display non distinct value in column. Is that possible?
Solved! Go to Solution.
@davehardikkumar
Place Column1 in a table visual along with the following measure
Column2Measure =
VAR T =
SUMMARIZE ( 'Table', 'Table'[Column1], 'Table'[Column2] )
RETURN
IF (
HASONEVALUE ( 'Table'[Column1] ),
SUMX ( T, 'Table'[Column2] ),
SUMX (
T,
IF ( COUNTROWS ( CALCULATETABLE ( 'Table' ) ) >= 2, 'Table'[Column2] )
)
)
Hi @davehardikkumar
Is this a data table or a table visual? Also please clarify further the desired output and present the complete picture.
its a table
and output should be
Column1 Coulmn2
A 10
B 20
C 30
------
Total= 10
@davehardikkumar
Place Column1 in a table visual along with the following measure
Column2Measure =
VAR T =
SUMMARIZE ( 'Table', 'Table'[Column1], 'Table'[Column2] )
RETURN
IF (
HASONEVALUE ( 'Table'[Column1] ),
SUMX ( T, 'Table'[Column2] ),
SUMX (
T,
IF ( COUNTROWS ( CALCULATETABLE ( 'Table' ) ) >= 2, 'Table'[Column2] )
)
)
Thank you. I have one more scenaio added to this table. there is third column.
Column1 Coulmn2 column3
A 10 Yes
B 20 No
C 30 Null
------ --------
Total= 10
desired total is 10 based on column3 value but column should also keep the other values. when I tried to use filter with suumarize in above code, it gives me the correct answer but it removes the other values from the cloumn. it just shows only 10 where as I have to display 10,20,30. How can I achive it?
Below is desired output. Many Thanks.
column2
10
20
30
----
total 10
Thanks. It worked! Appricate your help.
Thank you. I have one more scenaio added to this table. there is third column.
Column1 Coulmn2 column3
A 10 Yes
B 20 No
C 30 Null
------ --------
Total= 10
desired total is 10 based on column3 value but column should also keep the other values. when I tried to use filter with suumarize in above code, it gives me the correct answer but it removes the other values from the cloumn. it just shows only 10 where as I have to display 10,20,30. How can I achive it?
Below is desired output. Many Thanks.
column2
10
20
30
----
total 10
please try
Column2Measure =
IF (
HASONEVALUE ( 'Table'[Column1] ),
SUM ( 'Table'[Column2] ),
CALCULATE ( SUM ( 'Table'[Column2] ), 'Table'[Column3] = "Yes" )
)
@davehardikkumar First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8
User | Count |
---|---|
93 | |
37 | |
36 | |
15 | |
13 |
User | Count |
---|---|
99 | |
30 | |
29 | |
16 | |
16 |