Forum Discussion

aaronhowe's avatar
aaronhowe
Frequent Visitor
2 years ago
Solved

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

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 ...
Need Help
aaronhowe's avatar
aaronhowe
Frequent Visitor
2 years ago

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's avatar
ryan_mayu
Icon for Super User rankSuper User
2 years ago

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's avatar
    aaronhowe
    Frequent Visitor
    2 years ago

    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: