Forum Discussion

davehardikkumar's avatar
davehardikkumar
Helper I
3 years ago
Solved

distinct total

Hello Friends I need help with distinct total.     Below is table column1         Column2 A                      10 B                       20 A                       10 C                    ...
  • tamerj1's avatar
    tamerj1
    3 years ago

    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] )
        )
    )
tamerj1's avatar
tamerj1
Community Champion
3 years ago

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] )
        )
    )
davehardikkumar's avatar
davehardikkumar
Helper I
3 years ago

Thanks. It worked! Appricate your help.

  • davehardikkumar's avatar
    davehardikkumar
    Helper I
    3 years ago

    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

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      Hi davehardikkumar 

      please try

      Column2Measure =
      IF (
      HASONEVALUE ( 'Table'[Column1] ),
      SUM ( 'Table'[Column2] ),
      CALCULATE ( SUM ( 'Table'[Column2] ), 'Table'[Column3] = "Yes" )
      )