Search code examples
exceldaxmeasuredatamodel

Using DAX in Excel to reallocate values of blanks into a group

Data Rollup for Review - I have a value of hours that need to be rolled into their 'parent' values when the Pivot Table of my data has blank rows. I've managed to put the formula together in a calculated column, and returning the variable for each gives me exactly what I want on every line.

The problem lies in selecting which one to use when the Pivot Table is filtered. No matter what I do, I cannot get ISFILTERED to return a constant of either of the three columns I have to validate are filtered in order to select which variable to utilize. No matter what, the total of my hours needs to be the same, and all values in Layer 1 must be rolled up into Layer 2, and those Layer 1/Layer 2 items have to be rolled up into Layer 3 when whichever filtered column returns the least amount of rows.

Using ISFILTERED gives me the correct value when the Pivot Table is filtered, but applying that value as a constant appears literally impossible. Instead, I get the individual values of every row in the column, typically returning '1' because every row is 'filtered'.

Any help on this AT ALL would be greatly appreciated. There doesn't seem to be anything on this concept in YouTube videos, Goodle, Microsoft's website, and guides to Excel's Data Model is scarce if non-existent at this level.

Enclosed is the calculation my boss and I worked together to come up with over the past few weeks. As I said, using any of the 'Layer-#-Totals' variables works swimmingly...

=var totalEstHrs = [Task Est. Hours]
     var totalBaseHrs = [Task Hours Total]
     var amtToDistribute = CALCULATE([Task Est. Hours],ALL(Reporting),Reporting[Category]="Tasks",Reporting[Layer 1]="")
     var distributeValues = totalBaseHrs - amtToDistribute
     var Layer1Totals = IF(Reporting[Layer 1]="",0,CALCULATE((DIVIDE([Task Est. Hours],distributeValues,0) * amtToDistribute) + [Task Est. Hours]))
     var amtToDistribute2 = CALCULATE([Task Est. Hours],ALL(Reporting),Reporting[Category]="Tasks",Reporting[Layer 1]<>"",Reporting[Layer 2]="")+amtToDistribute
     var distributeValues2 = totalBaseHrs - amtToDistribute2
     var Layer2Totals = IF(Reporting[Layer 2]="",0,CALCULATE((DIVIDE([Task Est. Hours],distributeValues2,0) * amtToDistribute2) + [Task Est. Hours]))
     var amtToDistribute3 = CALCULATE([Task Est. Hours],ALL(Reporting),Reporting[Category]="Tasks",Reporting[Layer 1]<>"",Reporting[Layer 2]<>"",Reporting[Layer 3]="")+amtToDistribute2
     var distributeValues3 = totalBaseHrs - amtToDistribute3
     var Layer3Totals = IF(Reporting[Layer 3]="",0,CALCULATE((DIVIDE([Task Est. Hours],distributeValues3,0) * amtToDistribute3) + [Task Est. Hours]))
return

[Not sure what to return here...]

The Filter measures also come up w/ the correct counts:

Layer 1 Count:=if(
CALCULATE(
    COUNT(Reporting[Layer 1]),Reporting[Layer 1]<>"") = 0,
    CALCULATE(count(Reporting[Layer 1]),all(Reporting)),
        CALCULATE(COUNT(Reporting[Layer 1]),
Reporting[Layer 1]<>""))

(Layer 2 Count & Layer 3 Count are the same)

I'd love to find a solution to this, and will happily accept any assistance. My boss says we cannot use Power Query or Excel tables to do this, as we're trying to get this all solely into the Data Model.

Solution

  • My boss & I worked hard for weeks to figure this out. If it ends up helping someone, great. If not, well...

    First Measure:

    Layer1Group:=var _total1 = SUMX(FILTER(ALL(Reporting),Reporting[Layer 1]<>BLANK()),Reporting[Estimated Hours])
var _blankTotal1 = SUMX(FILTER(ALL(Reporting),Reporting[Layer 1]=BLANK()),Reporting[Estimated Hours])
var _layer1Total = SUM(Reporting[Estimated Hours])
var _percent1 = DIVIDE(_layer1Total,_total1,0)
return
_layer1Total + _percent1 * _blankTotal1

    Second Measure:

    Layer2Group:=var _total2 = SUMX(FILTER(ALL(Reporting),Reporting[Layer 2]<>BLANK()),Reporting[Estimated Hours])
var _blankTotal2 = SUMX(FILTER(ALL(Reporting),Reporting[Layer 2]=BLANK()),Reporting[Estimated Hours])
var _layer2Total = SUM(Reporting[Estimated Hours])
var _percent2 = DIVIDE(_layer2Total,_total2,0)
return
_layer2Total + _percent2 * _blankTotal2

    Third Measure:

    Layer3Group:=var _total3 = SUMX(FILTER(ALL(Reporting),Reporting[Layer 3]<>BLANK()),Reporting[Estimated Hours])
var _blankTotal3 = SUMX(FILTER(ALL(Reporting),Reporting[Layer 3]=BLANK()),Reporting[Estimated Hours])
var _layer3Total = SUM(Reporting[Estimated Hours])
var _percent3 = DIVIDE(_layer3Total,_total3,0)
return
_layer3Total + _percent3 * _blankTotal3

    Switch Measure:

    MySwitch:=SWITCH(TRUE(),
    ISFILTERED(Reporting[Layer 3]),[Layer3Group],
    ISFILTERED(Reporting[Layer 2]),[Layer2Group],
    ISFILTERED(Reporting[Layer 1]),[Layer1Group],
    [Task Est. Hours])

    Thank you to anyone/everyone who looked this over and considered helping but hadn't yet figured something out. :D