Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Difference Between Budget and Real - Two Datasets

Good Morning My Friends.   I'm new to PBI and did not find the solution to my problem seaching in the web.   I'll try to explain to you:   What I have are two differents Datasets, the first one...
Need Help
  • BA_Pete's avatar
    BA_Pete
    6 years ago

    Hi Anonymous ,

     

    I think your Table.Group in Power Query idea sounds fine. This is the way that I would solve the problem if I didn't need the extra detail from the Real table as it would push more calculation and processing away from the data model. I'd almost be tempted to put the variance column into Power Query as well, then just use a SUM([Variance]) measure to display the variance value.

     

    Pete

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous 

Create a date table

date = FILTER(ADDCOLUMNS(CALENDAR(DATE(2020,1,1),TODAY()),"year",YEAR([Date]),"month",MONTH([Date]),"monthname",FORMAT([Date],"mmm")),DAY([Date])=1)

Create a measure

$ from budget =
CALCULATE (
    SUM ( 'Table 2'[$] ),
    FILTER (
        'Table 2',
        'Table 2'[Departament] = MAX ( 'Table 3'[Departament] )
            && 'Table 2'[Account] = MAX ( 'Table 3'[Account] )
            && 'Table 2'[month no] = MAX ( 'Table 3'[Month] )
    )
)

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.