Forum Discussion

Mister_T's avatar
Mister_T
Advocate I
5 years ago
Solved

[DAX Measure] Correct values, but wrong total

Dear Community,

again, I am lost in the depth of Power BI and DAX. Any help greatly appreciated!


I have the following table 'Sales':

 

Deal ID

Deal Closed

Deal Won

Reseller

001

FALSE

FALSE

A

002

TRUE

FALSE

B

002

TRUE

FALSE

B

002

TRUE

FALSE

C

003

FALSE

FALSE

A

004

TRUE

FALSE

B

005

TRUE

TRUE

A

005

TRUE

TRUE

A

006

TRUE

TRUE

B

007

TRUE

FALSE

A

007

TRUE

FALSE

A

007

TRUE

FALSE

C

008

TRUE

FALSE

A

 

To find out, which reseller was involved in how many deals close lost, I have created the following measure (using help from this thread).

Please note: For one and the same deal (same Deal ID) it might happen, that

  • one reseller is listed several times --> then this reseller should only be counted once per deal
  • the deal is handled by more than one reseller --> then each of the resellers involved in the deal should be counted once
COUNT_Reseller_Lost = 
CALCULATE (
    DISTINCTCOUNT ( 'Sales'[Deal ID] ),
    FILTER (
        ALLEXCEPT ( 'Sales', 'Sales'[Reseller] ),
        'Sales'[Deal Closed] = TRUE ()
            && 'Sales'[Deal Won] = FALSE ()
        )
    )

 

This measure brings me the ring counts for each reseller, but unfortunately, the total is wrong!

How to I have to adjust the measure, that both the values for each Reseller and the Total are displayed correctly?

 

Thank you very much!

 

Mister_T

Need Help
  • ERD's avatar
    ERD
    5 years ago

    Hi Mister_T ,

    Create a second measure:

     

    #Result = 
IF(HASONEVALUE('Sales'[Reseller]),
[COUNT_Reseller_Lost],
SUMX(VALUES('Sales'[Reseller]), [COUNT_Reseller_Lost])
)

     

    If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

6 Replies

  • ERD's avatar
    ERD
    Community Champion
    5 years ago

    Hi Mister_T ,

    Create a second measure:

     

    #Result = 
IF(HASONEVALUE('Sales'[Reseller]),
[COUNT_Reseller_Lost],
SUMX(VALUES('Sales'[Reseller]), [COUNT_Reseller_Lost])
)

     

    If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

    • Mister_T's avatar
      Mister_T
      Advocate I
      5 years ago

      Thank you ERD!

       

      Works like a charm! 🙂

      Thanks again for your help! Greatly appreciated.

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Mister_T , Try

    COUNT_Reseller_Lost =
    CALCULATE (
    DISTINCTCOUNT ( 'Sales'[Deal ID] ),
    FILTER ('Sales' ,
    coalesce(CALCULATE(Max('Sales'[Deal Closed]) , ALLEXCEPT ( 'Sales', 'Sales'[Deal ID] )), false()) =True(),
    && 'Sales'[Deal Won] = FALSE ()
    )
    )

    • Mister_T's avatar
      Mister_T
      Advocate I
      5 years ago

      Hi amitchandak,

       

      Thank you very much!

      Unfortunately, your DAX formula returns
      "The syntax for '&&' is incorrect. (DAX(CALCULATE (DISTINCTCOUNT ( 'Sales'[Deal ID] ),FILTER ('Sales' ,coalesce(CALCULATE(Max('Sales'[Deal Closed]) , ALLEXCEPT ( 'Sales', 'Sales'[Deal ID] )), false()) =True(),&& 'Sales'[Deal Won] = FALSE ()))))."

       

      Deleting the ',' after "TRUE ()",

       

      COUNT_Reseller_Lost_NEW = 
CALCULATE (
    DISTINCTCOUNT ( 'Sales'[Deal ID] ),
    FILTER ('Sales' ,
        COALESCE (
            CALCULATE(
                Max('Sales'[Deal Closed]) ,
                ALLEXCEPT ( 'Sales', 'Sales'[Deal ID] )
                ),
            FALSE ()
            )
        =TRUE ()
        && 'Sales'[Deal Won] = FALSE ()
        )
    )

       


      lets the formula pass as measure, but as soon as I want to use it in a vizual, I get the error message


      Any alternative to 'MAX' when it cannot work with BOOLEAN?

      Thanks a lot!

       

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        Mister_T , Try like

        COUNT_Reseller_Lost_NEW = CALCULATE ( DISTINCTCOUNT ( 'Sales'[Deal ID] ), FILTER ('Sales' , COALESCE ( CALCULATE( Count('Sales'[Deal Closed]), 'Sales'[Deal Closed] = TRue() , ALLEXCEPT ( 'Sales', 'Sales'[Deal ID] ) ), 0 ) <>0 && 'Sales'[Deal Won] = FALSE () ) )