Forum Discussion

TM_'s avatar
TM_
Frequent Visitor
1 year ago
Solved

Cannot Distinct Count Negative Values

Hello.   Unsure what the issue is here - I created a measure to find the difference between two sets of values ('A' and 'B' for simplicity):   Difference =  IF(ISBLANK(A),  BLANK(),  (A- B)) ...
Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    Hi TM_ ,

    You can use the bellow DAX measure to achieve your goal:

    CategoryOutperformance = 
CALCULATE(
    DISTINCTCOUNT('Test2'[Category]),
    FILTER(
        ADDCOLUMNS(
            VALUES('Test2'[Category]),
            "Difference",
            VAR A = CALCULATE(SUM('Test2'[Value]), 'Test2'[Subcategory] = "A")
            VAR B = CALCULATE(SUM('Test2'[Value]), 'Test2'[Subcategory] = "B")
            RETURN IF(ISBLANK(A), BLANK(), A - B)
        ),
        [Difference] < 0
    )
)

     

    Your output should look like this:

     

rajendraongole1's avatar
rajendraongole1
Super User
1 year ago

Hi TM_  - It’s possible that there’s an issue with the current context or filter. Let’s try to modify your CategoryOutperformance measure to ensure it’s correctly filtering and counting the distinct categories.

 

CategoryOutperformance =
CALCULATE(
DISTINCTCOUNT('Test'[Category]),
FILTER(
'Test',
NOT(ISBLANK([Difference])) && [Difference] < 0
)
)

 

I hope it works, If you’re still having trouble, feel free to share more details or a sample of your data.

 

 

  • TM_'s avatar
    TM_
    Frequent Visitor
    1 year ago

    Hi rajendraongole1 

     

    Thank you for your reply! Unfortunately, this hasn't worked.

     

    More detail below, data set: 

     

     

    I used this formula in full to calculate the Difference where there was a value in Subcategory A: 

     

    Difference =
     VAR A =
     CALCULATE(
     SUM(
        Test2[Value]),
        Test2[Subcategory] = "A"
     )

     VAR B =
     CALCULATE(
     SUM(
        Test2[Value]),
        Test2[Subcategory] = "B"
     )

     RETURN

     IF(ISBLANK(A),
     BLANK(),
     (A - B))
     
    Result works fine it seems; difference is calculated correctly: 
     
    Then as per your reply, I tried this formula to distinct count the difference values which were negative - as above, this should be 6 but instead I get (Blank) on the card visualisation:
     
    CategoryOutperformance =
        CALCULATE(
            DISTINCTCOUNT(
            'Test2'[Category]
                ),
            FILTER(
                'Test2',
                    NOT(ISBLANK([Difference])) && [Difference] < 0
            )
        )
     
    Where could I be going wrong?
     
    Thank you. 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi TM_ ,

      You can create two measures as below to get it, please find the details in the attachment.

      Measure = 
VAR _diff = [Difference]
RETURN
    CALCULATE ( DISTINCTCOUNT ( 'Test2'[Category] ), FILTER ( 'Test2', _diff < 0 ) )
      CategoryOutperformance = SUMX ( VALUES ( Test2[Category] ), [Measure] )

      Best Regards