cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bi_poweruser429
Frequent Visitor

Measure is not referencing another measure correctly

Hi everyone,

 

I am new to these forums and I am coming across an issue that I have not seen referenced elsewhere, so I thought I would bring it here.  Essentially, I have a line chart that shows the performance of different products for certain metrics on a "months since" basis so that they can be compared based on time since inception rather than on linear time.  Below is a line chart for default rates, which is a simple formula (default dollars / accounts receivable dollars):

 

bi_poweruser429_1-1607019215946.png

 

Here is my issue.  I have a few metrics where instead of taking AR as my denominator, I am taking average AR over a rolling two month period which is defined by the following measure:

 

Average_AR = CALCULATE(SUM(FactMonthly[AR]),DATESINPERIOD(FactMonthly[Report_Date],LASTDATE(FactMonthly[Report_Date]),-2,MONTH))/2
 
Now, here's what happens if instead of dividing defaults by AR, I instead divide it by average AR:
 
incorrect.PNG
 

From what I can tell, not only is the default rate incorrect when dividing by average AR, the rate is simply double the amount in the top chart.  I'm really unsure of what could be causing this.  To give more background on my data model, I have a many-to-one relationship between my fact table and my date table:

 

data_model.PNG

 

My date table looks like this:

 

date_table.PNG

 

And additionally, I have an unconnected "Months Since Start" table which I iterate over with the following measure:

 

DQ Rate (Months Since) =
var FirstMonthNo =
MIN(FactMonthly[Month Number])

var LastMonthNo =
MAX(FactMonthly[Month Number])

var MonthNo = SELECTEDVALUE(DimMonthsSinceStart[Month])

return CALCULATE([Default Rate],
FILTER(ALL(DimDate),DimDate[MonthNoOverYears] = FirstMonthNo + MonthNo))
 
This measure works great for any rate that I need to calculate where the denominator is a column (like AR), but it screws up whenever the denominator is a measure (average AR).  Any help would be sincerely appreciated as I'm stuck on this problem and have not really found much in the way of similar issues on here.
 
Thank you
3 REPLIES 3
bi_poweruser429
Frequent Visitor

Hi Evelyn.  I unfortunately was not able to solve the problem in PowerBI itself due to time constraints.  What I ended up doing was to create a smaller table using Pandas, performing a groupby function and then using the .rolling() function to create an actual column for Total Average AR.  Not an optimal solution but this was the only way I was able to solve this in the given timeframe.  Thank you.

Eyelyn9
Community Support
Community Support

Hi @bi_poweruser429 ,

 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

 

Best Regards,
Eyelyn Qin

Eyelyn9
Community Support
Community Support

Hi @bi_poweruser429 ,

It's a little hard to follow.

According to your description, you could use AVERAGEX() or SUMX() or other functions to calculate Measure like: SUMX( FILTER ( TABLE, <CONDITION>), [Measure] ) 

 

Please provide me with more details about your table and your problem/expected output or share me with your pbix file from your Onedrive for Business WITHOUT sensitive data.

 

Best Regards,
Eyelyn Qin

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.