Forum Discussion
Excel calculation to Power BI Visual - Hierarchy calculations based on their row formula
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..
7 Replies
- AllisonKennedy
Community Champion
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)
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..
v-xuding-msftCommunity Support
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
