cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ReneDH
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

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 () )
    )

vluwangmsft_0-1624931633325.png

 

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 () )
    ))

vluwangmsft_2-1624932039342.png

 

 

Wish it is helpful for you!

 

Best Regards

Lucien

 

 

View solution in original post

7 REPLIES 7
v-luwang-msft
Community Support
Community Support

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 () )
    )

vluwangmsft_0-1624931633325.png

 

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 () )
    ))

vluwangmsft_2-1624932039342.png

 

 

Wish it is helpful for you!

 

Best Regards

Lucien

 

 

View solution in original post

v-luwang-msft
Community Support
Community Support

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 :

vluwangmsft_0-1624874474301.png

 

Best Regards

Lucien

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
Community Support
Community Support

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:

vluwangmsft_0-1624861015931.png

 

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()))

vluwangmsft_1-1624861132651.png

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

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

vluwangmsft_2-1624861212347.png

 

Wish it is helpful for you!

 

Best Regards

Lucien

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
Super User IV
Super User IV

@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...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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)

ReneDH_0-1624606107838.png

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)

ReneDH_1-1624606205079.png

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

Sample file - With data 

 



Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors