Double Group Custom Column Recalculation

[asalinas]

I am currently trying to find a way to trigger a column to recalculate if I regroup an already grouped data set.  For example, my current group shows values for each project and any WBS (sub project code) combinations that exist in my table.  I created a new column to perform a simple division to show me hours over quantity for each line item.  If I want to regroup to show that calculation at the WBS level but including all projects (they all have the same WBS structure), the calculation won't add up the grouped inputs, it will only sum the calculated column value for the new grouping.  Any ideas on how to dynamically recalculate my totals in query editor?

In short, I want to be able to calculate a new sum in column C and D when the values in column B match, but I don't want to remove the value in column A in order to sum the new grouping.  Once I get those new sums, I want to be able to re-divide to get my metric in column E.

Any help or ideas would be greatly appreciated.

[v-jiascu-msft]

Hi @asalinas,

Could you please mark the proper answer if it's convenient for you? That will be a help to others.

Best Regards!
Dale

[v-jiascu-msft]

Hi @asalinas

We could add two calculated columns to achieve this. Then we can redivide them.

New Total Del hrs by WBS =
CALCULATE (
    SUM ( Table1[Total WBS Del hrs] ),
    FILTER (
        'Table1',
        'Table1'[WBS-Level5(Description)] = EARLIER ( Table1[WBS-Level5(Description)] )
    )
)

New Total Del qty by WBS =
CALCULATE (
    SUM ( Table1[Total WBS del qty] ),
    FILTER (
        'Table1',
        'Table1'[WBS-Level5(Description)] = EARLIER ( Table1[WBS-Level5(Description)] )
    )
)

Best Regards!

Dale

[asalinas]

Hi Dale - Thank you for the calculated column instructions, that definitely helps us get to the numbers we need, however this specific request requires a drill down capability within our visualizations.  As such, is there a way to calculate the hrs/qty in one column that can be used to show individual project/wbs combinations as well as overall WBS totals?  Basically I want to be able to use the same column as my value in the visualization (which will be this calculated column) and chose my axis as either WBS or WBS per project.  I was able to accomplish this in a pivot table in excel, but don't know if that is possible in PowerBI as of yet, see below.

 

[v-jiascu-msft]

Hi @asalinas,

Maybe it's this one.

AllInONe =
VAR New_Total_Del_hrs_by_WBS =
    CALCULATE (
        SUM ( Table1[Total WBS Del hrs] ),
        FILTER (
            'Table1',
            'Table1'[WBS-Level5(Description)] = EARLIER ( Table1[WBS-Level5(Description)] )
        )
    )
VAR New_Total_Del_qty_by_WBS =
    CALCULATE (
        SUM ( Table1[Total WBS del qty] ),
        FILTER (
            'Table1',
            'Table1'[WBS-Level5(Description)] = EARLIER ( Table1[WBS-Level5(Description)] )
        )
    )
RETURN
    New_Total_Del_hrs_by_WBS / New_Total_Del_qty_by_WBS

Best Regards!

Dale