topic Re: Sum of Remaining Mini Values based on Department and Type in Matrix in DAX Commands and Tips

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

Power2BI — Fri, 08 Mar 2024 14:30:04 GMT

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,

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

lbendlin — Sat, 09 Mar 2024 01:54:37 GMT

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

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

talespin — Sat, 09 Mar 2024 02:52:33 GMT

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, "")

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

Anonymous — Mon, 11 Mar 2024 06:12:04 GMT

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.

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

Power2BI — Mon, 11 Mar 2024 20:42:12 GMT

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

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

Power2BI — Mon, 11 Mar 2024 20:43:14 GMT

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

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

Anonymous — Tue, 12 Mar 2024 06:57:19 GMT

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.

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

talespin — Tue, 12 Mar 2024 07:41:09 GMT

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

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

Power2BI — Tue, 12 Mar 2024 22:51:46 GMT

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 😞

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

talespin — Wed, 13 Mar 2024 05:22:23 GMT

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