topic Re: Measure that divides two filtered columns in DAX Commands and Tips

Measure that divides two filtered columns

Anonymous — Thu, 10 Sep 2020 15:43:55 GMT

Hello all,

I'm running into an issue where my calculated values are not showing the value I want. See table below for an example of a Project with 3 Work Orders. Each WO has a set number of locations to be completed.

Project	Work Type	Work Order	Completed	Not Started	In Progress	Total Construction Locations
Western 25	GR OH	123456	83	0	0	83
Western 25	GA Feeder	234567	0	8	0	8
Western 25	GA Lateral	345678	0	34	0	34

I'm trying to look at the Project as a whole and find the % of completed locations. I would like for it to calculate 83 Completed/125 Total Locations = .664 or 66.4%. However, when I created a column using the formula below, it just gave me 3 rows of 25.04%, which summed to 75.1% (below).

% of Completed = sum('S123 WOs'[Completed Locations by WO])/sum('S123 WOs'[Total Construction Locations])

Can someone help me write the correct formula for a measure to give me 83/125 = .66.4% instead of 75.1%? Keep in mind that the table also contains other Projects, though I listed just one example. Thanks.

Robyn

Re: Measure that divides two filtered columns

Greg_Deckler — Thu, 10 Sep 2020 15:51:38 GMT

Anonymous

I think:

% of Completed = sum('S123 WOs'[Completed Locations by WO])/CALCULATE(sum('S123 WOs'[Total Construction Locations],ALL('S123 WOs'[Total Construction Locations]))

Re: Measure that divides two filtered columns

Anonymous — Thu, 10 Sep 2020 16:19:41 GMT

Hi Greg_Deckler ,

I am getting an error for "Too many arguments were passed to the SUM function. The maximum argument count for the function is 1."

☹️

Robyn

Re: Measure that divides two filtered columns

Greg_Deckler — Thu, 10 Sep 2020 16:23:30 GMT

Anonymous Missed a paren

% of Completed = sum('S123 WOs'[Completed Locations by WO])/CALCULATE(sum('S123 WOs'[Total Construction Locations]),ALL('S123 WOs'[Total Construction Locations]))

Re: Measure that divides two filtered columns

Anonymous — Thu, 10 Sep 2020 16:26:06 GMT

Perfect. This worked! I've been going in circles trying to figure it out, but I was missing the ALL function.

Thank you so much! #lifesaver