locked
Calculate number of orders within budget RRS feed

  • Question

  • Hi,

    I have to calculate how many orders that are within budgettet margin. I have three tables, one with the order numbers, one with actual margins and a table with budgetted margins.

    I tried the following, but unsuccesfull:

    CM2 Performance Ratio =

    Calculate(

    (DISTINCTCOUNT(Orders[Project definition]));

    Filter('Actual';sum('Actual'[CM2 EUR])>=sum(Budget[Contract CM2 EUR]))

    )

    I am not sure that I am even remotely close to the correct solution, so input are most welcome.

    Monday, January 11, 2016 8:21 PM

Answers

  • ActualAmount:= SUM( 'Actual'[CM2 EUR] )
    
    BudgetAmount:= SUM( 'Budget'[Contract CM2 EUR] )
    
    ProjectsOverBudget:=
    COUNTROWS(
        FILTER(
            VALUES( 'Order ID'[Project Definition] )
            ,[ActualAmount] > [BudgetAmount]
        )
    )

    The SUM()s are trivial. COUNTROWS() is trivial. FILTER() takes a table as input, creates a row context for each row in that table by iterating one-by-one through the rows of the table, evaluating the expression in its second argument in each row context, and returns a table made up of all rows for which the expression returned true.

    The table is VALUES( 'Order ID'[Project Definition] ), which gives us the distinct values of 'Order Id'[Project Definition] in the current filter context.

    GNet Group BI Consultant

    • Marked as answer by Morten_DK Tuesday, January 12, 2016 8:42 PM
    Tuesday, January 12, 2016 8:24 PM

All replies

  • Hi Morten,

    It's hard for us to understand you requirement based on your description, so we cannot give you the detail solution for this requirement.

    In DAX expression, we can use both LookUpValue function and Related function to return a related value from another table. In your scenario, are there any relationship between those tables? Please provide us more information about your data structure, and provide us some sample data. If possible, provide us some screenshots about it, so that we can make further analysis. Here are some useful links for you reference.
    https://msdn.microsoft.com/en-us/library/gg492170.aspx
    https://msdn.microsoft.com/en-us/library/ee634202.aspx

    Regards,


    Charlie Liao
    TechNet Community Support

    Tuesday, January 12, 2016 2:19 AM
  • Hi Charlie,

    My tables looks like this:

    So the "Project Definition" is the relation between the tables.

    What I want to do, is to create a measure in the "Order ID" table which counts the number of orders where CM2 from the "Actual" table was better or equal to the CM2 on the same order in the "Budget" table.

    I tried to show it in a Pivot table below:

    Where the gray columns is what I would like to calculate.

    I hope the above can help understand my requirement.

    Tuesday, January 12, 2016 8:13 PM
  • ActualAmount:= SUM( 'Actual'[CM2 EUR] )
    
    BudgetAmount:= SUM( 'Budget'[Contract CM2 EUR] )
    
    ProjectsOverBudget:=
    COUNTROWS(
        FILTER(
            VALUES( 'Order ID'[Project Definition] )
            ,[ActualAmount] > [BudgetAmount]
        )
    )

    The SUM()s are trivial. COUNTROWS() is trivial. FILTER() takes a table as input, creates a row context for each row in that table by iterating one-by-one through the rows of the table, evaluating the expression in its second argument in each row context, and returns a table made up of all rows for which the expression returned true.

    The table is VALUES( 'Order ID'[Project Definition] ), which gives us the distinct values of 'Order Id'[Project Definition] in the current filter context.

    GNet Group BI Consultant

    • Marked as answer by Morten_DK Tuesday, January 12, 2016 8:42 PM
    Tuesday, January 12, 2016 8:24 PM
  • Thank you very much - works very well.
    Tuesday, January 12, 2016 8:43 PM