cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## Distributing Value into specified category

I have the following dataset and a seperate total amount that varies, let's say at this time it is -150. I need to distribute that -150 to specific categories untill -150 is at 0. For example the -150 will first go to Dog - 30, making Dog - 0, and then we have -120 that will go into Groceries - 400, making groceries 280, and that takes up all of the original -150 so it doesnt need to be distributed further.

The way I am currently doing it is causing memory issues, so I am hoping you all know of better way to do it.

Thanks!

1 ACCEPTED SOLUTION
Community Support

I have created a  simple sample, please refer to it to see if it helps you.

Create measures.

``````Measure =
VAR _2 =
IF ( MAX ( 'Table'[Index] ) >= 4, [Budget by categorym], BLANK () )
RETURN
SUMX (
FILTER ( ( 'Table' ), 'Table'[Index] >= SELECTEDVALUE ( 'Table'[Index] ) ),
_2
)
``````
``````Measure2 =
VAR _1 = - ( -150 )
VAR _2 =
SUMX (
FILTER ( ALL ( 'Table' ), 'Table'[Index] <= SELECTEDVALUE ( 'Table'[Index] ) ),
[Measure]
)
VAR _re =
IF ( _1 <= _2, MAX ( 'Table'[purchase] ), BLANK () )
RETURN
_re
``````

``````result =
VAR _1 =
MINX ( FILTER ( ALL ( 'Table' ), [Measure 2] <> BLANK () ), 'Table'[Index] )
RETURN
IF (
MAX ( 'Table'[Index] ) <= _1
&& [Measure] <> BLANK (),
MAX ( 'Table'[purchase] ),
BLANK ()
)
``````

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

Community Support

I have created a  simple sample, please refer to it to see if it helps you.

Create measures.

``````Measure =
VAR _2 =
IF ( MAX ( 'Table'[Index] ) >= 4, [Budget by categorym], BLANK () )
RETURN
SUMX (
FILTER ( ( 'Table' ), 'Table'[Index] >= SELECTEDVALUE ( 'Table'[Index] ) ),
_2
)
``````
``````Measure2 =
VAR _1 = - ( -150 )
VAR _2 =
SUMX (
FILTER ( ALL ( 'Table' ), 'Table'[Index] <= SELECTEDVALUE ( 'Table'[Index] ) ),
[Measure]
)
VAR _re =
IF ( _1 <= _2, MAX ( 'Table'[purchase] ), BLANK () )
RETURN
_re
``````

``````result =
VAR _1 =
MINX ( FILTER ( ALL ( 'Table' ), [Measure 2] <> BLANK () ), 'Table'[Index] )
RETURN
IF (
MAX ( 'Table'[Index] ) <= _1
&& [Measure] <> BLANK (),
MAX ( 'Table'[purchase] ),
BLANK ()
)
``````

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors