Need help in DAX formula

[Ashish9201]

Hi, 

I have created a average function which is as below

"Average = (DISTINCTCOUNT(merged_allocation_daily[Project Sys ID])/(DISTINCTCOUNT(merged_allocation_daily[Crew Sys ID])))"

However the count of project is calculating incorrectly, Below is the screenshot of the matrix created using the formula, the # of projects is coming up incorrect, which should be 347 for HMCA, 316 for HMIN, 215 for HMUK and 755 for HMUS.

Could you please help me in correcting the sum of Projects and Average formula?

Thank you so much!

Ashish

[AnalyticsWizard]

Hi @Ashish9201 

Here’s how you can achieve this:

1. Create a New Measure:

   • Go to the Modeling tab in Power BI.
   • Click on New Measure.
   • Name your measure (e.g., “Crew with 5+ Projects”).

2. Calculate Crews with 5 or More Projects:

   • Use the following DAX formula for your new measure:

     Crew with 5+ Projects =
     CALCULATE(
         [Distinct Crew Count],
         FILTER(
             ALL(merged_allocation_daily),
             [Distinct Crew Count] >= 5
         )
     )

   • Replace [Distinct Crew Count] with your existing measure for distinct crew count (e.g., DISTINCTCOUNT(merged_allocation_daily[Crew Sys ID])).

3. Update Your Visual:

   • Now, use the new measure “Crew with 5+ Projects” in your visual instead of the previous “# of Projects” measure.
   • Apply any necessary filters to your visual.

[Ashish9201]

Hi,

I was able to fix the formula and have created below two to resolve the issue.

1) # of Projects = SUMX(VALUES(merged_allocation_daily[Project Sys ID]),CALCULATE(DISTINCTCOUNT(merged_allocation_daily[Crew Sys ID])))

2) Average = DIVIDE([# of Projects],DISTINCTCOUNT(merged_allocation_daily[Crew Sys ID]) )

Below is the snap of the visual i created using the formulaes,

I am working on to update the visual for crew members to be including in the above visual only if they have worked on 5 or more than 5 projects, when i apply the filter on "# of projects" it only works on the subtotal rather than each line item.

Could you please help me with updating my formulas to including crew members working on 5 or more than 5 projects?

Thank you in advance!

Ashish

[Ashish9201]

Hi Team,

Can someone please help me with solution on updating my formulas to including crew members working on 5 or more than 5 projects?

Thank you,

Ashish Gupta

[v-kongfanf-msft]

Hi @Ashish9201 

To correct the calculation of the number of items, you need to make sure that you are using the correct columns in the DISTINCTCOUNT function. It appears that the problem may be related to the way you are calculating the different item IDs.

Modify your fomula like below:

Average =
VAR TotalProjects =
    DISTINCTCOUNT ( merged_allocation_daily[Project Sys ID] )
VAR TotalCrews =
    DISTINCTCOUNT ( merged_allocation_daily[Crew Sys ID] )
RETURN
    DIVIDE ( TotalProjects, TotalCrews, 0 )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Ashish9201 ,  Try a measure like

Sumx(Values(merged_allocation_daily[Crew Company]), calculate(DISTINCTCOUNT(merged_allocation_daily[Crew Sys ID]) ))