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
Icon for Community Champion rankCommunity 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
    Icon for Community Champion rankCommunity 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?

       

      • v-danhe-msft's avatar
        v-danhe-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        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