incorrect totals in tables with multiple relationship and circular dependency

[prakritnepal-cn]

I am struggling with using sumx and coming up with totals in my BI dashboard.  I am calculating revenue YTD which includes a series of calculation to reach the revenue number. it is based on budgets(data in the tables) and % completions(calculated) of those budgets. My YTD number is not totalling accurately. My relationships:

my job table is connected to acc master with jobnum. The purpose of account master is mostly just to connect the jobs to account balances which has revenue, cost information by period. Each period is a column - no dates, one to many relationship from job to acc master

acc master to acc balance is one to many, account ID is the join

I have 12 measures for both each month of revenue and cost (24 totals for these) to calculate the changes in that month + prior year changes and beginning balances based on required criteria within acc balance tables. I am trying to create a revenue YTD measure which is dynamic that changes as period selection.

my budget formulas are something like this: 

var per = SELECTEDVALUE('Month-selector'[Month])

var jan = [beginning bud] + [jan rev bud] +[prior year rev]

var feb = jan + [feb rev bud]

var mar = feb + [mar rev bud]

var apr = mar + [apr rev bud]

var may = apr + [may rev bud]

var jun = may + [jun rev bud]

var jul = jun + [jul rev bud]

var aug = jul + [aug rev bud]

var sep = aug + [sep rev bud]

var oct = sep + [oct rev bud]

var nov = oct + [nov rev bud]

var dec = nov + [dec rev bud]

var bud = SWITCH(TRUE(),

per ="1", jan,

per ="2", feb,

per ="3", mar,

per ="4", apr,

per ="5", may,

per ="6", jun,

per ="7", jul,

per ="8", aug,

per ="9", sep,

per ="10", oct,

per ="11", nov,

per ="12", dec

)

return

-bud

All those measures used in the measure above are something like this: 

CALCULATE(SUM('F0902-acc balances',filtercriteria))/100

There is a % completion calculation which follows similar process, that % is multiplied to the budget formula above to get the revenue number.

Issues I am dealing with: 

1. The total doesn't add up for the measure

2. I created a calculated column in a reference table to get these data. When I try to have a monthly revenue amount using similar formula (instead of selected value I enter 1, 2 or something like that) I get circular dependency on the second formula every time. For eg: I create january amount which in above example selected value would be "1". That works fine, as soon as I try to create for feb, circular dependency! so the calculated column instead of measure is not working either. I know the reason why, mentioned here: https://www.sqlbi.com/articles/understanding-circular-dependencies/ . I tried the solution in the article, i could not figure it out. 

What I am trying to build:

1. Get the YTD amounts dynamically using selected value (slicer in the report)

2. Get month amounts so I can do Month over month calculations and Quarter over quarter calculations

I would appreciate any help on this. Thank you.

[Anonymous]

Hi @prakritnepal-cn ,

Could you please provide the screenshots about your table visual with data and Fields setting of this table visual just as shown in below screenshot? Also please explain which table or measure these fields come from.

Note: The screenshots need to mark the wrong values, what is the correct value and the related calculation logic.

Best Regards

[prakritnepal-cn]

Merged the tables to be able to use related function and getting this error when trying that way. Any suggestions? Issue with Filter statement criteria, not sure how to avoid this. The columns in question are all type text. 

[Anonymous · Accepted solution]

Hi @prakritnepal-cn ,

Please create another new measure as below to instead of the measure [ZrevenueYTD] on the visual in order to resolve the incorrect total value of ZrevenueYTD:

Measure = SUMX ( VALUES ( 'yourtable'[Job Description] ), [ZrevenueYTD] )

In addition, you can refer the method in the following links to resolve it.

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

And for the problem in your last post, please update the formula of your measure [apr bud] as below and check whether it works or not:

Note: The part with red font is updated one.

apr bud =
DIVIDE (
    SUMX (
        FILTER (
            'Merged',
            'Merged'[f0902-acc balances.jobnum] = RELATED ( 'F0006-job'[jobnum] )   //whether this filter condition can be deleted?
                && 'Merged'[f0902-acc balances.Ledger Type] = "JA"
                && 'Merged'[f0902-acc balances.object account]
                IN { "1210", "1270", "1250", "1215", "1255", "1273" }
        ),
        'Merged'[f0902-acc balances.apr]
    ),
    100,
    0
)

Best Regards

[prakritnepal-cn]

Thank you. It worked. used sumx and filter conditions inside the expression. 

removed related from the formulas

[prakritnepal-cn]

Revenue YTD(Calculated Column) is the correct one

Z revenueYTD is incorrect as you can see the total is incorrect on that one:

Revenue YTD is calculated by series of custom columns: 

Cost actuals : calculate(sum of all period columns YTD from acc balances table, filter (a bunch of criterias on different columns)

cost budget: similar calculation as cost actuals

% completed : cost actuals/cost budget

Revenue YTD = revenue Budget * % completed

ZrevenueYTD is a measure. The formulas are basically the same except this one is a measure instead of calculated column. Also uses selected value to determine YTD calculation (full formula mentioned in the initial post)

I have also tried sumx to see if I can get get correct totals but I think I need to use related function here and I am getting stuck on that piece trying that solution. The value for monthly numbers need to be in millions and it comes out in billions. My Sumx formula is something like this: Sumx (Filter(table, series of criterias),account balance month)