Forum Discussion

Zarlot531's avatar
Zarlot531
Icon for Helper V rankHelper V
6 years ago
Solved

Tricky divide by "Distinct Count" problem

So below is a sample dataset to help clarify what I'm trying to do. I want to know how much we spent on, say, the "Dog" vendor divided by ALL distinct jobs, regardless of whether we spent money at th...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Zarlot531 ,

     

    Measure =
VAR ave =
    SUM ( 'Table'[Amount] )
VAR dis =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Job] ), ALL ( 'Table' ) )
RETURN
    DIVIDE ( ave, dis )

    Please refer to the pbix as attached.

     

ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Zarlot531 , 

you have to remove the filter from Vendor like so:

 

Avg across all jobs =
VAR SumVendor =
    SUM ( Table1[Amount] )
VAR NoOfAllJobs =
    CALCULATE ( DISTINCTCOUNT ( Table1[Job] ), ALL ( Table1[Vendor] ) )
RETURN
    DIVIDE ( SumVendor, NoOfAllJobs )