Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Community,
I am trying to get a running total on a field based on condition on a different column.
Below is my input table along with expected output column (Cumulative Assigned)to produce . Column to run a cumulative sum is "Assigned" and Condition is to run cumulative until "Resource Plan" reaches max value for that period. In this case max value ocurs at FY20-P10.
Fiscal Year | GSI Name | GD SI Role | Month | Resource Plan | Assigned | Cumulative Assigned |
FY20 | X | Offshore | FY20-P08 | 61 | 40 | 40 |
FY20 | X | Onsite | FY20-P08 | 41 | 20 | 20 |
FY20 | X | Offshore | FY20-P09 | 66 | 23 | 63 |
FY20 | X | Onsite | FY20-P09 | 44 | 12 | 32 |
FY20 | X | Offshore | FY20-P10 | 126 | 41 | 104 |
FY20 | X | Onsite | FY20-P10 | 84 | 43 | 75 |
FY20 | Y | Offshore | FY20-P08 | 67 | 60 | 60 |
FY20 | Y | Onsite | FY20-P08 | 45 | 40 | 40 |
FY20 | Y | Offshore | FY20-P09 | 56 | 55 | 115 |
FY20 | Y | Onsite | FY20-P09 | 33 | 32 | 72 |
FY20 | Y | Offshore | FY20-P10 | 100 | 76 | 191 |
FY20 | Y | Onsite | FY20-P10 | 78 | 54 | 126 |
I have tried Summarize columns function but result is as not as expected.
Note ; Offshore and onsite values should not be summed up together. (Offshore-->Offshore and Onsite-->Onsite for each period), gorup by each GSI Name
Thanks
HI @v-amarsh,
SUMMARIZECOLUMNS function will return a table, you can't directly use it in a calculated column. I'd like to suggest you aggregate with the result table(Greg_Deckler's suggestion) or create a new calculated table with your formulas. (notice: please add 'allselected' functions to ignore current category group or formula will only calculate on row contents of current category group)
Cumulative Table=
SUMMARIZE (
'Role Master',
'Role Master'[GD SI Role],
'Role Master'[Resource Plan],
'Role Master'[GSI Name],
'Role Master'[Fiscal Year],
'Role Master'[Month],
'Role Master'[Forecast Resource Plan],
'Role Master'[Assigned],
'Role Master'[Date],
"Running Cumulative Assigned", CALCULATE (
SUM ( 'Role Master'[Assigned] ),
FILTER (
ALLSELECTED ( 'Role Master' ),
'Role Master'[Resource Plan] <= EARLIER ( 'Role Master'[Resource Plan] )
),
VALUES ( 'Role Master'[Assigned] )
)
)
Regards,
Xiaoxin Sheng
Not an answer, but similar idea to the Pareto chart in Excel. Unfortunately the Pareto chart doesn't exist in Power BI, but there have been a few blog posts around the internet on how to create them. The order changes based on the group total, so depending what you need, adding an index column prior to any calculations may not be the solution you need. Then again, this may be a step further than you're looking for.
Thanks for your reply chandrenstrom. I have tried using DAX version equivalent for Parento chart in Excel by some internet sources. However, the result is giving me scalar value of the total sum of values .
Thanks
HI @v-amarsh,
If my formula not helpful, can you please share a pbix file with some dummy data and expected results to test?
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi @v-shex-msft
Thanks for your reply. Please find the attached .pbix file. The table on the RHS is the output from the DAX formula.
SUM function used in summarize will always give sum for a group together rather than cumulative progression sum what is expected.
Note : My cumulative sum should stop once it reaches the MAX value found in Resource Plan for that particular period. In this dummy data set, max value is seen for period FY20-P10. This can vary.
I am unable to attach my .pbix here so sending the link for report. Request you to download .pbix from that link.
Thanks
Hi @v-amarsh,
I can't access your link, can you please upload the sample to 'onedrive for business' and share the link here?
Regards,
Xiaoxin Sheng
Hi @v-shex-msft
I have uplaoded the file to One drive for business and shared with you. Below is the link.
Thanks
Hi @v-amarsh,
I test the modified formula and it can output the expected result as you shared, please try it if it meets your requirement:
Cumulative Table =
SUMMARIZE (
'Role Master',
'Role Master'[GD SI Role],
'Role Master'[Resource Plan],
'Role Master'[GSI Name],
'Role Master'[Fiscal Year],
'Role Master'[Month],
'Role Master'[Assigned],
"MyExpectedOutputColumn",
VAR maxMonth =
CALCULATE (
MAX ( 'Role Master'[Month] ),
FILTER ( ALLSELECTED ( 'Role Master' ), [Assigned] <> BLANK () ),
VALUES ( 'Role Master'[Fiscal Year] ),
VALUES ( 'Role Master'[GSI Name] ),
VALUES ( 'Role Master'[GD SI Role] )
)
RETURN
IF (
RIGHT ( [Month], 2 ) < RIGHT ( maxMonth, 2 ),
CALCULATE (
SUM ( 'Role Master'[Assigned] ),
FILTER (
ALLSELECTED ( 'Role Master' ),
RIGHT ( [Month], 2 ) <= RIGHT ( EARLIER ( 'Role Master'[Month] ), 2 )
),
VALUES ( 'Role Master'[Fiscal Year] ),
VALUES ( 'Role Master'[GSI Name] ),
VALUES ( 'Role Master'[GD SI Role] )
)
)
)
Regards,
Xiaoxin Sheng
I don't see anything where you have a sortable column that would allow you to define "previous". If you had that, you could do something like this:
Column =
SUMX(
FILTER(
'Table',
[GSI Name] = EARLIER('Table'[GSI Name]) &&
[GD SI Role] = EARLIER([GD SI Role]) &&
[Some field] <= EARLIER([Some field])
),
[Plan Assigned]
)
User | Count |
---|---|
18 | |
11 | |
5 | |
4 | |
3 |