Modelling Data from a Cube and compare % of Categories and Subcategories

[ELToninio]

Hi everybody,

I am working for a big company handling an enormous amount of data. Aside of SAP to proceed to data collection we have another software used for the reporting of Actuals and Data Inputs for different budget scenarii. This reporting software is a CUBE, so it is working like a huge and powerful PivotTable. However the problem is that PivotTable/Cube type of datas are not "friendly" as it is not raw data. 

And I am not sure how to model this data first on the cube to export on excel and then model it in PowerBI.

Here are screenshots of 2 examples of what I thought to do.

First example : I selected in the CUBE, the Actuals data only. So I could Have one table of actuals and one for the budget.

Columbs are divided by : Department/Direction number. Each direction has multiple Cost Centers, and each cost centers have different types of costs. I thought in this case I could link the table Actuals with the Budget one through the common key by merging the 3 first columns to make it look like as the following : "Direction 01 - CC01 - Wedges"

==> 

Second possible way.

Instead of having 2 tables, I regroup everything in one table. Now I added from the cube, the budget aspect.  So each line compare to the previous Table, is doubled from the fact that each line will have one from the actuals and one from the budget.

In both cases, my idea is to unpivot those date columns to reproduce a format of "raw datas". I will also drill down the Directions and Cost centers.

Which approch do you think is the best ? Because in reality I have not only 1 budget setting but 2-3 different budgets phases. So is it better to separate tables in order to compare them ?

My end goal is to weight in % the proportion of each nature of cost versus all the costs of a given cost center.

For example how much does the wedges of the CC01 represents versus all its costs ? but also versus the entire Direction 01 or versus all Cost center Wedges. 

I also want to weight the impact of a direction vs other directions/grand total. 

How is it possible to do so with Measures ? Do I have use DAX measure CALCULATE + ALL and draw each possible analysis ? Or there is a faster and more efficient way to make PowerBI understand I want to compare each Hierarchy ?

Thank you very much for your help,

Antoine

[ELToninio]

I am actually really struggling, because if I merge my actual and budgets tables through a comon key and end up with 1 table. I have no dimension table to organize my model and I don't know how to measure the variances between the actuals and 2 other budgets.

Because if I regroup all in one table through merging with a common key, then on the same line i have the ID, the Actual cost, the budget 1 and the budget 2.

Isn't better to create link between tables ? How should I proceed ?

Thanks again

[rajendraongole1]

Hi @ELToninio -You can create separate tables for Actuals and Budgets. This approach allows you to clearly distinguish between different scenarios (e.g., Actuals vs. Budgets) and phases of budgets (multiple budget settings). By merging the key columns (Department/Cost Center/Cost Type), you can then relate the two tables using these keys. In Power BI, this method would give you flexibility when creating visuals by switching between or comparing the actual and different budget scenarios.you can keep the Actuals and Budgets in separate tables, but create a unified model by relating the two using the common key (e.g., Department, Cost Center, Cost Type).

When unpivoting the columns, keep a "scenario" column (Actual/Budget) to ensure you can filter and calculate measures based on the selected scenario.

eg : Percentage of Cost Type within Cost Center:

CostTypePercentage_CC =
DIVIDE(
SUM('Actuals'[Cost Amount]),
CALCULATE(SUM('Actuals'[Cost Amount]), ALL('Actuals'[CostType])),
0
)

CostTypePercentage_Direction =
DIVIDE(
SUM('Actuals'[Cost Amount]),
CALCULATE(SUM('Actuals'[Cost Amount]), ALL('Actuals'[Cost Center])),
0
)

find the difference measure with actual and budget similarly. 

By setting up the model this way, you can create multiple visuals to display comparisons across hierarchies in Power BI. Drillthrough and hierarchy slicers can also be used to explore your data at different levels easily.

Hope this helps.

[ELToninio]

Good evening @rajendraongole1,

it seems that my previous reply didn't get through, or maybe it is delayed.

Thank you very much for this insight.

For the formula number 2, is it about the % of a type of cost of all cost centers combined within a Direction right ?

I also wanted to know 2 things :

1) What if I want to display the % of cost center (all cost combiened) vs all costs centets within all directions.

And if i wanted to measure the % of a department all cost combined vs all the other departments? 

What that formula be ?

Sorry I am very new to PowerBI 🙂 

Oh and I wanted to know if in my case, a Date Table (calendar) is necessary ? 

The Actuals will only be expressed for the on going year on a monthly baisis

The budgets will be made with quarterly reference only. 

For example I will also want to compare the Budget number 2 with the budget made in budget number 1 realised earlier during the year. 

By the way would a waterfall chart be interesting here to understand what are the differences in the type of costs and its budget evolution.

Thanks again 🙂 

[ELToninio]

Hello @rajendraongole1 thanks a lot for this great insight and help.

I will try this out tomorrow at work 🙂 

The first formula as mentionned the % of a cost type within the cost center.

The second formula is the % of a cost type within its direction, right ? In other words % a cost type of all cost centers belonging in the same Direction ?

Now if want the % of all cost within a Direction vs ALL direction, will the formula be the following : 

DIVIDE(
SUM('Actuals'[Cost Amount]),
CALCULATE(SUM('Actuals'[Cost Amount]), ALL('Actuals'[DIRECTION])),
0

thank you so much

)