Dynamic changing of measure name and value


#1

Hi Fellow Power BI Users -

I am working on a report where user want to see Actual values for a completed period and budget value for rest of the year. the column heading should also change along with the data . i mean to say, if for a period there is actual, the column heading should show actuals and data its data, when actuals is 0 for the period it should show budget and its data.
Please can you provide some tips

Thanks
Swapnil


#2

Will need some more information to help you out. Please provide more details about what you actually want to accomplish. Sam has video’s available for budgeting, and then showing how to put that data in a chart to compare actual vs budget data. I would suggest watching the video’s if you have not first to get you going in the right direction. Here are some video’s I would suggest:



Thanks
Jarrett

Enterprise%20DNA%20Expert%20-%20Small


#3

Thanks for sharing the links Jarrett. below is my example for the table expected

Would be great if you can provide tips to achieve this.


#4

The key here is quite simple, you need to unpivot the data so that the table becomes long and thin. (Months in one column and budget amount in another column). This is how you want all your table to look like in Power BI.

Once you have done this, there is all the information you need to make that analysis of this data possible within the training material.

Jarrett has added some good videos. Also check out the below inside Enterprise DNA Online.


#5

Thanks Sam for the related videos. However, my requirement is more specific around what to show. My data model has period data in column. Actual and budget I have calculated based on a flag. Now if you see the report layout, I need to see actuals as header if actual data exist if not it should show budget. Hope you can understand and help me with it.
Thanks
Swapnil


#6

Hi,

So you wish the output in a Power BI visual (matrix) to show this.
I would try to use the SWITCH function and show the actuals measure when the date is <=Last day in your actuals and the Budget measure if not. Adding conditional formatting will do the job.

Paul

Enterprise%20DNA%20Expert%20-%20Small


#7

Hi Paul,
Below is my measure calculation. i do not have any date in the data model. i tried using IF but that doesnt seem to work.

Actual/Budget = 
    VAR Actual = CALCULATE( [PTD], FILTER( GL_Balances,GL_Balances[ACTUAL_FLAG] = "A"))
    VAR BudgetVal = CALCULATE( [PTD], FILTER( GL_Balances,GL_Balances[ACTUAL_FLAG] = "B"))
    RETURN
IF( Actual <> 0, Actual,BudgetVal )

#8

Hi,

I am at a loss how you wish to visualise actuals vs budget without a date column in the data and a proper date table?

Paul

Enterprise%20DNA%20Expert%20-%20Small


#9

Hi Paul,

Don’t want to see Vs budget in report, just actual if it’s there for the period if not budget


#10

Will likely need more information here to get a solid answer for you.

Further info around the model and data structure, and also how you want to show this is important (ie. the context of the calculation)

Also it concerns me that you don’t have any dates in your model…basically you should. Any analysis around time should be completed with a date table.