Calculate a cost % for a specific category

[james_pease]

Good monring everyone,

I am still working on a creating a stand alone regression formula in dax that will continuously calculate everytime a new row of data is added to the SQL server. In my quest to solve this, I used excel to conduct regression analysis and found my line equation which happens to have a logarithmic trend. So I can create this measure but after a month or two, I would need to recalculate to get a more accurate y-intercept and slope, hence why I am working on creating a dax expression that conducts the calculations for me.

So, I have broken down the simple y = A+b*ln(x) formula into the separate more complicated parts, calculating the y-intercept and the slope. I created a formula but I know the answer is wrong becuase I am expecting to see a number between 0 and 1. Where I think the issue is, is that the formula I created is calculating the entire cost % for everying (this is my y). I need to filter out only a specific cost category, I am looking to create 2 separate dax expressions for the regression calculations, one for "Store Level Labor" and the other for "Food Cost." These will be my two y values for the separate expressions. My x-value is "Actual Sales Null Values." These are aggregates for every store/company which I intend to use data slicers to filter by company. (5 total)

So I need to help with the current expression, I am expecting to see a number between 20% to 40% but I am getting 88% which is total cost:

Labor Cost = (calculate(Sum('All_Actual_Expenses (2)'[Amount]),
Filter('ChartOfAcounts', ChartOfAcounts[Category] = "Store Level Labor"))
/
Sum('All_Actual_Sales (2)'[Actual Sales Null Values]))

I am using the table on the left as a reference to verify if my calculations "look" accurate. Clearly my y-intercept is wrong, and my xy values are not appearing. I think this stems from my y-value calculated column being incorrect, which I think stems from the above Labor Cost expression not filtering "Store Level Labor."

[james_pease]

I have also tried the following:

Labor Cost = Calculate(DIVIDE(

                Sum('All_Actual_Expenses (2)'[Amount]),

                Sum('All_Actual_Sales (2)'[Actual Sales Null Values])

            ),

            Filter(

                'ChartOfAcounts',

                ChartOfAcounts[Category] = "Store Level Labor"

            )

)

but the returned value is blank.

Labor Cost =

                VAR LaborPercentage = Filter(ChartOfAcounts,[Category] = "Store Level Labor")

                Return

                DIVIDE(

                    Sum('All_Actual_Expenses (2)'[Amount]),

                    Sum('All_Actual_Sales (2)'[Actual Sales Null Values]))

This returns 88% which is the total cost % again.

[v-easonf-msft · Accepted solution]

Hi， @james_pease 

Not fully sure what you want.

Please try formula like:

Labor Cost =
CALCULATE (
    SUM ( 'All_Actual_Expenses (2)'[Amount] ),
    FILTER ( 'ChartOfAcounts', ChartOfAcounts[Category] = "Store Level Labor" )
)
    / CALCULATE (
        SUM ( 'All_Actual_Sales(2)'[Actual Sales Null Values] ),
        FILTER ( 'ChartOfAcounts', ChartOfAcounts[Category] = "Store Level Labor" )
    )

Best Regards,
Community Support Team _ Eason

[james_pease]

So sorry last thing, do you know why SUMX isnt calculating per row?

Labor Cost =

CALCULATE (

    SUMX( 'All_Actual_Expenses (2)', 'All_Actual_Expenses (2)'[Amount]),

    FILTER ( 'ChartOfAcounts', ChartOfAcounts[Category] = "Store Level Labor" )

)

    / CALCULATE (

    SUMX ( 'All_Actual_Sales (2)',[Actual Sales Null Values]),

    FILTER ( 'ChartOfAcounts', ChartOfAcounts[Category] = "Revenue" )

)

[james_pease]

Thank you so much, I figured it out, I needed to remove the filter line from "actual sales null values". Thanks again!!!!

[james_pease]

When I used this formla for a calculated column, the result is infinity for every row. When I make this a measure, the result is NaN. 

[james_pease]

Thank you, I will try this today and let you know if it worked.