Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

distinct total

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?

1 ACCEPTED 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] )
        )
    )

View solution in original post

8 REPLIES 8
tamerj1
Super User
Super User

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

Hi @davehardikkumar 

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors