Calculation Groups: Showing Actuals, YTD but how to display full year budget

[ReneDH]

All,

I'm trying to get the following to work:
I've created a nice set of measures which will consume my values from my fact table into usable KPIs.

The fact table contains the following:
Date, Location, Actuality, Code, Value

The data that i'm using is only monthly so i've created a date table with all dates and connected it to my dataset.

I'm want to use calculation groups so that for every measure I can use  the acutality information (stating if it is actual numbers, budget or forecast numbers).

So to for the actual number:

CALCULATE ( SELECTEDMEASURE (), 'Data'[Actuality] = "AC")

For the YTD measure:

CALCULATE (
      SELECTEDMEASURE (),
      DATESYTD ( 'FiscalYear'[Date], "30/9" ),
        'Data'[Actuality] = "AC"
    )

And for budget:

CALCULATE ( SELECTEDMEASURE (), 'Data'[Actuality] = "Budget" )

When I then create a slicer containing the year/month selection and have a matrix table containing all the info it works for 95%.
It displays the selected actuals as expected, the YTD is working fine.

However for budget it calculates the budget only for the selected period but what I would like that it calculates this part of the measure for the full year instead of only the year/month selection.

[v-luwang-msft · Accepted solution]

Hi @ReneDH ，

Costs per volume = 
VAR SUMCOST =
    CALCULATE (
        SUMX ( 'Data', [Costs] ),
        FILTER ( ALL ( Data ), Data[Actuality] = MAX ( Data[Actuality] )&&Data[Location]=MAX(Data[Location]))
    )
VAR SUMvolume =
    CALCULATE (
        SUMX ( 'Data', [Volume] ),
        FILTER ( ALL ( Data ), Data[Actuality] = MAX ( Data[Actuality] )&&Data[Location]=MAX(Data[Location]) )
    )
RETURN
    IF (
        MAX ( Data[Actuality] ) <> "Budget",
        DIVIDE ( [Costs], [Volume], BLANK () ),
        DIVIDE ( SUMCOST, SUMvolume, BLANK () )
    )

And if you want YTD shows all locations value ,use the below:

Costs per volume = 
VAR SUMCOST =
    CALCULATE (
        SUMX ( 'Data', [Costs] ),
        FILTER ( ALL ( Data ), Data[Actuality] = MAX ( Data[Actuality] )&&Data[Location]=MAX(Data[Location]))
    )
VAR SUMvolume =
    CALCULATE (
        SUMX ( 'Data', [Volume] ),
        FILTER ( ALL ( Data ), Data[Actuality] = MAX ( Data[Actuality] )&&Data[Location]=MAX(Data[Location]) )
    )     VAR SUMCOST2 =
    CALCULATE (
        SUMX ( 'Data', [Costs] ),
        FILTER ( ALL ( Data ), Data[Actuality] = MAX ( Data[Actuality] ))
    )
VAR SUMvolume2 =
    CALCULATE (
        SUMX ( 'Data', [Volume] ),
        FILTER ( ALL ( Data ), Data[Actuality] = MAX ( Data[Actuality] ))
    )
RETURN
    IF (
        MAX ( Data[Actuality] ) ="Ac",
        DIVIDE ( [Costs], [Volume], BLANK () ),IF( MAX ( Data[Actuality] ) ="Budget",
        DIVIDE ( SUMCOST, SUMvolume, BLANK () ),DIVIDE ( SUMCOST2, SUMvolume2, BLANK () )
    ))

Wish it is helpful for you!

Best Regards

Lucien

[v-luwang-msft]

Hi @ReneDH ,

Delete the two measure about sum,then use the following measure:

Costs per volume =
VAR SUMCOST =
    CALCULATE (
        SUMX ( 'Data', [Costs] ),
        FILTER ( ALL ( Data ), Data[Actuality] = MAX ( Data[Actuality] ) )
    )
VAR SUMvolume =
    CALCULATE (
        SUMX ( 'Data', [Volume] ),
        FILTER ( ALL ( Data ), Data[Actuality] = MAX ( Data[Actuality] ) )
    )
RETURN
    IF (
        MAX ( Data[Actuality] ) <> "Budget",
        DIVIDE ( [Costs], [Volume], BLANK () ),
        DIVIDE ( SUMCOST, SUMvolume, BLANK () )
    )

Final :

Best Regards

Lucien

[ReneDH]

Almost there, but now the trick is that if i'm adding multiple locations. To the table.
The formula is calculating the budget for all locations combined and doesnt show them seperate per row.

[v-luwang-msft]

Hi @ReneDH ,

As I understand it, what you are trying to achieve is that Buget displays a full year's worth of data, as shown below：

And try the following steps:

Step1,create the following measure:

SUMCOST = CALCULATE(SUMX('Data',[Costs]),FILTER(ALL(Data),Data[Actuality]=MAX(Data[Actuality])))

SUMvolume = CALCULATE(SUMX('Data',[Volume]),FILTER(ALL(Data),Data[Actuality]=MAX(Data[Actuality])))

Then change Costs per volume  to below:

Costs per volume = IF(MAX(Data[Actuality])<>"Budget",DIVIDE([Costs], [Volume], BLANK()),DIVIDE([SUMCOST],[SUMvolume],BLANK()))

If you also want the actual show the data all the year,use the below:

Costs per volume = DIVIDE([SUMCOST],[SUMvolume],BLANK())

Wish it is helpful for you!

Best Regards

Lucien

[ReneDH]

This works indeed, however there is the need of 2 additional measures.
Is it also possible to fix this without those 2 measures?

I've tried to add this this in the cost per volume measure however that won't work.
Also to filter it in a later stage at the calulation groups are not giving the right output.

This because I have several measures which are calulating a actual, and full year budget, which would mean for every measure i need to add additonal measures to support my full year budget overview.

[amitchandak]

@ReneDH , there few ways to show the same budget for a full year

This year Sales = CALCULATE(SUM(Table[Budget]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))

another way is to store budget on last date year and use CLOSINGBALANCEYEAR 

Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

[ReneDH]

Hi thanks for the reply however this is not exactly what I'm looking for.
I've created a measure (costs per volume) which will calculate the costs for making the products.
These are all from my fact table.

With the help of calculation groups i'm generating for the measure (costs of volume) the actual figure, the YTD and the budget.
If I select all dates it calculates the this across all dates so the actual = YTD.
And the budget is calulated for the whole year so this is right (7.59)

But what I try to achieve is when i select 1st of june, we see the actual of that month which is 10, the YTD is still correct. However it shows now the budget for June only, whilst I want to show the budget for the whole fiscal year (7.59)

I've created this sample file to show what is happening

Sample file - With data