Forum Discussion

JCK2's avatar
JCK2
Helper III
6 years ago
Solved

Dax column calculation

Hello,   i want to create a measure to arrive at one number as how much is due in the month, I wrote teh below calculation in column, however this is not helping me achive what i want.   How does...
Need Help
JCK2's avatar
JCK2
Helper III
6 years ago

Thanks a lot Greg!! Yes you are right...Spain should be over due....! 

 

For the total due, instead of counting it as 1, if we use distinct count of the id's, we are there...currently this is the result..because 1 audit can be done at two places with same id and we want to calculate it as 1.

 

i did tried calculation to replace 1 by disntict count of ID's but no luck...:( still see the same numbers..

 

 

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

JCK2 - are you looking for this?

 

Measure 2 = 
    DIVIDE(
        COUNTROWS(FILTER(FILTER('Table',DISTINCT('Table'[Id])),NOT(ISBLANK([Total Overdue 1])) && [Total Overdue 1] = 0)),
        SUM('Table'[Total Due 1])
    )
  • JCK2's avatar
    JCK2
    Helper III
    6 years ago

    Greg_Deckler  Thanks a lot for your effort and patience, it worked :)!

     

    I started using Powerbi from last week, always have been using Alteryx and Tableau...this was my first challange!!

     

    Appreciate your help in onboarding and making me love PowerBi!!

     

    Look forward to keep learning more things from you Greg!! You are a True Guru :)! Thanks once again!

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Apologies, maybe this:

    Measure 2 = 
    DIVIDE(
        COUNTROWS(FILTER(SUMMARIZE('Table',[Id],"__Count",IF(NOT(ISBLANK(MAX('Table'[Total Overdue 1]))) && MAX([Total Overdue 1]) = 0,1,0)),[__Count]=1)),
        SUM('Table'[Total Due 1])
    )
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago
    Anytime!
  • JCK2's avatar
    JCK2
    Helper III
    6 years ago

    Greg, do you have a pbx file? Thanks