Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
miracle2023
Helper I
Helper I

Running Total of multiple measures

Hi folks!

I'm currenrly stucked with occured issue. I want to create measure over "PredictedDueOverdueStatic" measure which is given below. All my trials returned either very big number or the same number as "PredictedDueOverdueStatic" measure. 

I have DimDate table with single relationship with facttable[case_deadline] and employeetable[date]. 

I have following measures:

 

 

 

 

Current_overdue = 
CALCULATE(SUM(facttable[cases]),table[status]="Overdue")

Current_due = 
CALCULATE(SUM(facttable[cases]),table[status]="Due")

Sum = 
SUM(table[sum_cases])

Delivery = (105.5*[EmployeeAllocation])/28.6 
&&
EmployeeAllocation = 
var _fte = SUMX(FILTER(EmployeeTable,CONTAINSSTRING(EmployeeTable[TaskName],"highrisk")),EmployeeTable[declaredhours]/8)
var days = 
CALCULATE(
    DISTINCTCOUNT(EmployeeTable[Date]),
    FILTER(
        EmployeeTable,
        WEEKDAY([Date],2)<6
        && EmployeeTable[Date] <= TODAY()
))
var _f = CALCULATE(DIVIDE(_fte,days,0))
return 
_f

PredictedDueOverdueStatic = 
[Sum] - [Delivery]


 

 

 

 

 

Tabke.png

Any help would be valuable. 

Thank you.

4 REPLIES 4
miracle2023
Helper I
Helper I

bump.

Thanks

miracle2023
Helper I
Helper I

Any help would be very valuable. 

Thanks

sevenhills
Super User
Super User

Without the sample data (model) and/or the .pbix file tough to suggest.

 

Assuming you have YYYY-MM and use that in the visual and try first.

Second step, replace with the year and month as separate columns

 

PredictedDueOverdueStatic = 

-- Running Total
IF (
   -- To avoid showing for no sum rows
   not ISBLANK(table[Sum]),  

-- RT Calculation
SUMX( 
   -- Get the list of all dates, in my case I have YYYY-MM in the visual
   filter(ALLSELECTED( 'Date'), 'Date'[YYYY-MM] <= SELECTEDVALUE('Date'[YYYY-MM]) ), 

   -- sum diff of two measures
   [Sum] - [Delivery]
) 

)

 

 

 

---------- below works ------------------

Based on the Adventure works dw 2020, I can recommend this ... 

 

 

 

-- Measures created
Total Sales = sum(Sales[Sales Amount])

North America Sales = 
SUMX( FILTER( ALLSELECTED('Sales Territory'), 'Sales Territory'[Group] = "North America" ), [Total Sales])

 

 

 

 

RT measure:

 

 

 

RT Sales - Rest of the world = 

IF (
-- To avoid showing for no sales months
not ISBLANK(Sales[Total Sales]),  

-- RT Calculation
SUMX( 
   -- Get the list of all dates, in my case I have YYYY-MM in the visual
   filter(ALLSELECTED( 'Date'), 'Date'[YYYY-MM] <= SELECTEDVALUE('Date'[YYYY-MM]) ), 

   -- Sum diff of two measures
   [Total Sales] - [North America Sales]
) 

)

 

 

 

 

sevenhills_0-1696272240559.png

 

 Hope this gives some direction for your need!

 

Hi, 

Thanks for your suggestion.

Unfortunately, it did not help even with some minor measure tuning. 

I changed my SQL query to get all customers id so I used calculate(count),status="Due". For overdue I created calculated column with the following logic: if status="Overdue" then return today date. then count of a new column with userrelationship in it. Then sum overdue+due. Another steps are similar. 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors