Sum of Remaining Mini Values based on Department and Type in Matrix

[Power2BI]

Hello PBI Community,

I'm working on report to calculate expenses status based in Departmnet and Type as in screenshot below. Due to remaining calculation after each sepnding and budget type I need to add remaining in Matrix visula, however the sum of remaining is worng it should be Sum of Mini values per each expense type in TOTAL without effect the remaining column rows values as in last column. Attached the excel samplefile, please support:

Sample excel link: Link 

Thanks,

[talespin]

hi @Power2BI 

If you need Calculated column, please see solution from @Anonymous 

If you need a measure, please see below.

You need to add Index in Power Query. Then use this measure.

Remaining Amount =

VAR _Index = SELECTEDVALUE('Table'[Index2])

VAR _Budget = CALCULATE( SUM('Table'[Budget]), REMOVEFILTERS('Table'[Expense Label]) )

VAR _Spent = IF(

                HASONEFILTER('Table'[Expense Label]),

                CALCULATE( SUM('Table'[Expenses]), REMOVEFILTERS('Table'[Expense Label]), 'Table'[Index2] <= _Index),

                CALCULATE( SUM('Table'[Expenses]), REMOVEFILTERS('Table'[Expense Label]) )

            )

RETURN _Budget - _Spent

Sharing file as well

https://drive.google.com/file/d/118kPlIHpow9KjNhok2zHtjdybMxZlape/view?usp=sharing

[Power2BI]

@talespin Yes this working well on Expense type level, however in expense level keep showing wrong value when I moved the new measure as screen shot below and attached file (NEW PowerBI sample link 😞

[talespin · Accepted solution]

hi @Power2BI 

This is happening because previously you had different label names under Title > Expense type but now you have same label name and multiple rows under Expense Type.

1. Please use this measure, I have changed how Index value is derived.

2. Please correct Index, In Power Query Column should be sorted by Title > Expense type > Label (all ascending order), then add Index column.

= Table.Sort(#"Changed Type",{{"Title", Order.Ascending}, {"ExpenseType", Order.Ascending}, {"Label", Order.Ascending}})

Remaining Measure =

VAR _Index = CALCULATE( MAX('Table'[Index2]) )

VAR _Budget = CALCULATE( SUM('Table'[Budget]), REMOVEFILTERS('Table'[Label]) )

VAR _Spent = IF(

                HASONEFILTER('Table'[Label]),

                CALCULATE( SUM('Table'[Expenses]), REMOVEFILTERS('Table'[Label]), 'Table'[Index2] <= _Index),

                CALCULATE( SUM('Table'[Expenses]), REMOVEFILTERS('Table'[Label]) )

            )

RETURN _Budget - _Spent

[Anonymous]

Hi @Power2BI ,

You can create a calculated column to calculate the minimum remaining value.

Min Remaining =
VAR _mini = MINX(FILTER(ALL('Table'), 'Table'[ExpenseType] = EARLIER('Table'[ExpenseType]) && 'Table'[Budget] = 0), 'Table'[Remaining])
RETURN
IF('Table'[Remaining] = _mini,_mini,BLANK())

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Power2BI ,

@talespin  provided a good method of applying measure to solve the problem. I have a method of applying calculated column to solve the problem. I hope it can be helpful to you.

1. Create a new index column in the power query editor for subsequent calculations.

2. Create a calculated column to get the remaining value.

remain =
VAR _sum_spent = CALCULATE(SUM(Sheet2[Spent]),FILTER(ALL('Sheet2'),'Sheet2'[Department] = EARLIER('Sheet2'[Department]) && 'Sheet2'[Type] = EARLIER('Sheet2 '[Type]) && 'Sheet2'[Index] <= EARLIER('Sheet2'[Index])))
VAR _sum_budget = CALCULATE(SUM(Sheet2[Budget]),FILTER(ALL('Sheet2'),'Sheet2'[Department] = EARLIER('Sheet2'[Department]) && 'Sheet2'[Type] = EARLIER('Sheet2 '[Type])))
RETURN
IF('Sheet2'[Spent] = BLANK(),BLANK(),_sum_budget - _sum_spent)

3. Create a calculated column to obtain the minimum value of the remaining values.

mini =
VAR _mini = CALCULATE(MIN('Sheet2'[remain]),FILTER(ALL('Sheet2'),'Sheet2'[Department] = EARLIER(Sheet2[Department]) && 'Sheet2'[Type] = EARLIER(Sheet2[ Type])))
RETURN
IF('Sheet2'[remain] = _mini,_mini,BLANK())

The final result is shown in the figure below, and more details can be found in the attachment.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Power2BI]

Thanks @Anonymous  for proposing this solution. Unfortuntly didnt work for me as I tried to use the same Measure in my report but didnt give me correct remainig number.

Here attached another sampe file which might be helpful to know more about my issue (PowerBI Sample data link).

Thanks again, looking forwared to hear back from you ..

AMD

[talespin]

hi @Power2BI 

Somthing like this?

Remaining =

VAR _Dept = SELECTEDVALUE('Table'[Department])

VAR _Type = SELECTEDVALUE('Table'[Type])

VAR _Index = SELECTEDVALUE('Table'[Index])

VAR _Budget = CALCULATE( SUM('Table'[Budget]), ALLEXCEPT('Table', 'Table'[Department], 'Table'[Type]) )

VAR _Spent = IF(HASONEFILTER('Table'[Department]) && HASONEFILTER('Table'[Type]),

                CALCULATE( SUM('Table'[Spent]), ALLEXCEPT('Table', 'Table'[Department], 'Table'[Type]), 'Table'[Index] <= _Index ),

                CALCULATE( SUM('Table'[Spent]), ALLEXCEPT('Table', 'Table'[Department], 'Table'[Type]) )

)

RETURN IF( ISBLANK(SELECTEDVALUE('Table'[Budget])), _Budget - _Spent, "")

[Power2BI]

Thanks @talespin  for proposing this solution. Unfortuntly didnt work for me as I tried to use the same Measure in my report but didnt give me correct remainig number.

Here attached another sampe file which might be helpful to know more about my issue (PowerBI Sample data link).

Thanks again, looking forwared to hear back from you ..

AMD

[lbendlin]

Your data is missing an index column.  Power BI has no idea how to sort it, or what "Remaining" refers to.