Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX Calculation for Aggregated values inside single table

Hi all, I need a little DAX help!   I have the following table structure:   Customer NID AgentCount AAA 15897457 1 AAA 67845217 4 AAA 58579248 3 AAA 82465752 4 BBB 5...
Need Help
Show and Tell
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    Hi Anonymous,

    Based on my test, you could refer below steps to get the Calc1:

    Create a calculated column:

    Column = CALCULATE(COUNT(Table1[Customer]),FILTER('Table1','Table1'[Customer]=EARLIER(Table1[Customer])))

    Create a measure and you could get the correct result:

    Calc1 = CALCULATE(COUNT(Table1[Column]),FILTER('Table1',Table1[Column]=1))/DISTINCTCOUNT(Table1[Customer])

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

PattemManohar's avatar
PattemManohar
Community Champion
7 years ago

Anonymous Your expected output wasn't clear. Could you please post the expected output data as well as per sample input data.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    So just to clarify, from the sample table above.

     

    Calc1:

    - There are just 2 customers where there is only a single instance of them, "DDD" and "EEE".

    - In total there are 6 unique customers, AAA, BBB, CCC, DDD, EEE, and FFF.

    - Expected result is (2/6) = 33.33%

     

    Calc 2:

    -There are 4 NIDs that have a AgentCount of 1, NIDs. 15897457, 84511198, 80970254, 93915454

    - There are 22 NIDs (or 22 records in total)

    - Expected result is (4/22) = 18.18%

     

    I hope that helps.

    • PattemManohar's avatar
      PattemManohar
      Community Champion
      7 years ago

      Anonymous Thanks for making it clear. Here are the two measures you are looking for.

       

      Test20Calc1 = 
      VAR _TotalUniqCount = DISTINCTCOUNT(Test20Measures[Customer])
      VAR _SingleCustCount = COUNTROWS(FILTER(SUMMARIZECOLUMNS(Test20Measures[Customer],"Cnt",COUNTROWS(Test20Measures)),[Cnt]=1))
      RETURN (_SingleCustCount/_TotalUniqCount)*100 
      Test20Calc2 = 
VAR _DistinctCount = COUNTROWS(FILTER(Test20Measures,Test20Measures[AgentCount]=1))
VAR _TotalRows = COUNTROWS(Test20Measures)
RETURN (_DistinctCount/_TotalRows)*100

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Thanks, the 2nd calc works fine.

         

        However im getting error with calc1:

         

         

        NormalisedDays=TableName

        FCRv1 = MeasureName

         

        Im not even using AddMissingItems() in the calculation. Any Ideas?