Converting a rank/count/value apportionment formula from Excel to BI

[aaronhowe]

I have a formula in Excel which takes a total cost value and apportions it against various grades, which are ranked.  The quantity of a particular ranking can vary by month and by factor (shown here as Colour) but the total value will be a set figure each month.  For example:

Those formulas:

Ranking formula:		    COUNTIF($B$2:$B$10,"<="&B2)
Apportionment formula:		$F$1*(COUNTIF($B$2:$B$11,"<="&B2)/SUM(C$2:C$11))

I can't seem to translate this to BI however.  Essentially I need to 1) count the number of instances of a given grade in a single month/month end, 2) sum the count of (1)) for a given month, then divide 1) by 2).  It seems like it should be simple but I can't seem to get a grip on the formula flow in DAX and my brain is melting as a result.

If I have the following data:

Month End	Colour	Grade	COUNTIF	As %
31/12/2023	Blue	A	1	5%
31/12/2023	Blue	B	2	11%
31/12/2023	Blue	B	2	11%
31/12/2023	Blue	C	2	11%
31/12/2023	Red	    C	2	11%
31/12/2023	Yellow	D	1	5%
31/12/2023	Green	E	3	16%
31/12/2023	Orange	E	3	16%
31/12/2023	Purple	E	3	16%

Then  I'd like to be able to allocate 5% of the total costs against an A grade, ~21% against a B grade, ~21% against C, 5% against D and ~48% against E.

I can't seem to replicate the combination of COUNTIF, percentage over a set of criteria (grade and month end), and grouping those values.  Actual grouping in Power Query didn't help unfortunately.  Is there something more simple I'm missing here?

[aaronhowe]

Thank you @ryan_mayu, that worked.  I need to figure out now how to apply it per-site manager but I think I can do that, your assistance is really appreciated!

[ryan_mayu]

you are welcome

[ryan_mayu]

@aaronhowe 

pls try this to create the ranking

ranking = countx(FILTER('Table','Table'[Value]<=EARLIER('Table'[Value])),'Table'[Value])

what's the output for appd formular?

[aaronhowe]

Hi @ryan_mayu, sorry for the delay in replying - unfortunately I'm only on this project part time.  The output of the Appd formula in "Value Appd" is just the sum of the cost involved divided over the percentage applied per grade weighting.

I'm getting an error in the EARLIER function that the parameter is not of the right type - I'm not sure what I should have been using there.  The table is named 'Combined Site Margin' and contains a few key fields: Site Manager (text field), Direct Cost (sum of costs for the site manager, decimal field), Grade (text field), Index (an integer listing A, B, C as 1, 2 3, etc) and Month End (date/calendar field).

If I'm using EARLIER I assume I should be using it for a numeric type, so using the Index field should have been okay but it's refusing to accept it?

[ryan_mayu]

@aaronhowe 

are you createing a column or a measure? pls try to create a column

how many tables do you have?

could you pls just add the output in the screenshot of the excel?

[aaronhowe]

Cleary I need more coffee @ryan_mayu as I was creating a measure.  I switched it to a column and got a result, albeit a strange one so I think I'm still not going the right way.

I'm not sure I understand the request re the excel file.  Data as shown:

As formulas:

[ryan_mayu · Accepted solution]

@aaronhowe 

created both for columns and measures

pls see the attachment below