Excel calculation to Power BI Visual - Hierarchy calculations based on their row formula

[Anonymous]

we have a report where we have some calculations in the excel that needs to create in Power BI visual. 

Below is the excel report screenshot : 

Excel to power bi visual

Highlighted in green color is my requirement for report.

Highlighted in red color are ITEM_ID that are stored in fact table. 

There is one to many relationship between ITEM_ID and the measures "Actual" & "Budget".

Below is the screenshot of the fact table.

Requirement is to calculate sum of Actual & Budget on the basis of ITEM IDs formula for every row.

For example : To calculate Productive summary > % NIT in report = sum of actual amount where ITEM_IDs are (810+550) /  (550+808+ 810+830)

To calculate Contractors > Staff = sum of actual amount where (ITEM ID is 690 divide by ITEM_ID is 410).

Do we need to create a table to store  % holidays, % % unsold T&M/FP, % NIT, Staff, Purchase costs etc ..information as we don't have such information in the table.

Can we have any trick to calculate the measure for each row..

[AllisonKennedy]

@Anonymous  Not sure what you mean by relationship between MEASURE and fact table, Measures act within the context of the report, but they are not housed in a table and therefore do not have relationships as such to tables, aside from what is built within their definition. 

Can you please share the DAX formula for these MEASURES, or if they are actually columns then please share a preview of that table as well.

There are ways to get what you want using the CALCULATE function and putting the Item ID = 580 || Item ID = 590 etc as the filter

|| is the OR condition 

However, it would be preferable to setup a robust data model and think about why you are calculating for (810+550) /  (550+808+ 810+830) . Ideally, that information is also stored in the Fact table or data model as a category or subcategory id which groups the individual IDs (ie Products Sold / All Products in Category you would use the SUM(budget)/CALCULATE(SUM(budget), CatID=Prod)

With your data as given, you would be looking at doing something similar to: 

MEASURE = CALCULATE( [budgetmeasure], Fact[Item ID]=810 || Fact[Item ID] =550) /  CALCULATE( [budgetmeasure], Fact[Item ID]=810 || Fact[Item ID] =550 || Fact[Item ID]= 808 || Fact[Item ID] =830)

[Anonymous]

Custom Hierarchy

[v-xuding-msft]

Hi @Anonymous ,

I think you mean show measures on rows. Please try to turn on the option:

Reference:

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-august-2017-feature-summary/#valuesOnRows

[Anonymous]

Hi  v-xuding-msft,

No, I don't want to display measures on rows.. 

Here, My requirement is to display mesaures "Actual" & "Budget" based on manual hierarchy depends on ITEM_ID calculation.

Below image is to display on rows :

manual hierarchy

[Anonymous]

Hi v-xuding-msft,

If I display measure on rows. How will I get two column values as per requirement. Below is the screenshot and column values are higlighted in yellow color.

[v-xuding-msft]

Hi @Anonymous ,

Sorry that I am still not clear. Due to the part of sample data and they are not totally same as the ones that highlighted with red, I don't know how to calculate out the measures of Actual and Budget based on ITEM IDs. How do we distinguish between both of them? And for the values of Productive summary and Contractors, are they from the existing column? Or they need to be created manually?

Can you please share more sample data and the logic of calculation?  

[Anonymous]

Apologies for creating confusion. I was taling about relationships between dimension and fact table..

There is no DAX formula for measures.. Below is the screenshot of the fact table.

Actual Quantity and Budget quantity are the measures.

we just need to SUM of measures from above fact table.

I gone through your measure..

MEASURE = CALCULATE( [budgetmeasure], Fact[Item ID]=810 || Fact[Item ID] =550) /  CALCULATE( [budgetmeasure], Fact[Item ID]=810 || Fact[Item ID] =550 || Fact[Item ID]= 808 || Fact[Item ID] =830)

We can work like this.. But how will we make custom hierarchy based on the calculation.. because we don't have such information in the tables.. we have to create hierarchy depends on the item IDs calculation... 

Hope this clears to you.

Let me know if you need more information..