Forum Discussion

asalinas's avatar
asalinas
New Member
9 years ago

Double Group Custom Column Recalculation

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 ...
Need Help
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

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's avatar
    asalinas
    New Member
    9 years ago

    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's avatar
      v-jiascu-msft
      Microsoft Employee
      9 years ago

      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