Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Calulcated table with multple columns

I'm looking to create new calucated table.  I've looked at multiple solutions but so far no luck

 

Tables:
Direct Org A (Group, BU, Division)
BP Bridge B (Burden Pool)
Bonus Budget C (Division Budget)
Bonus Actual D (Bonus Actual)

 

SQL Query

Select A.group, a.bu, a.division, b.burden_pool, c.division_budget, d.bonus_actual
from Direct Org A,
BP Bridget B
LEFT JOIN Bonus Budget C ON B.BPDIV = C.BPDIV,
LEFT JOIN Bonus Actual D ON B.BPDIV = D.BPDIV
Where a.bpdiv = b.bpdiv and b.bpdiv = c.bpdiv and c.bpdiv = d.bpdiv
and (c.division budget <> 0 and d.bonus actual <> 0)

 

DAX Query

EVALUTE
SUMMARIZE('Direct Org'
,BP Bridge[Burden Pool]
,"Div Buget", 'Bonus Budget'[Division Budget],
,"Div Actual",'Bonus Actual'[Bonus Actual],

 

Need Help

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

       

      This does seem to work.  Do I really need the aggregations 

       

      EVALUATE SUMMARIZECOLUMNS('Direct Orgs'[Direct Group], 'Direct Orgs'[Direct Business Unit], 'Direct Orgs'[Direct Division], 'BP Bridge'[BURDEN_POOL], FILTER(VALUES('Bonus - Budget'[DIVISION_BUDGET]), ('Bonus - Budget'[DIVISION_BUDGET] <> VALUE("0"))), "Division Budget", [Division Budget], "Bonus Actual YTD Non-Blank", [Bonus Actual YTD Non-Blank], "Bonus Remaining Non-Blank", [Bonus Remaining Non-Blank])

      • v-danhe-msft's avatar
        v-danhe-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi Anonymous,

        Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

         

        Regards,

        Daniel He