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 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
    Microsoft 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