Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
Solved! Go to Solution.
Hi @KrikkitWars42 ,
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 ()
)
How to Get Your Question Answered Quickly
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.
Hi @KrikkitWars42 ,
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 ()
)
How to Get Your Question Answered Quickly
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.
User | Count |
---|---|
136 | |
70 | |
69 | |
54 | |
52 |
User | Count |
---|---|
207 | |
94 | |
64 | |
61 | |
57 |