cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RAVISHhcl
New Member

Multiple Return rows in SUMX

Hi All,

 

I am trying to write a DSO Countback DAX measure. My data table looks like below -- 

My requirement is to calculate the DSO values dynamically on the Country and Customer level using the same table.

RAVISHhcl_1-1638217517650.png

 

My Dax works fine, but it return two set of values -- the formula uses two return statement 

 

[o:2021.03,i:2021.03,ocurrARBal:25,icurrRev:40,Country:IN,icurrAR:25,revSUMXP:,revSUMX:40,pleft:25,cleft:-15,multiplier:0.625,result:18.75] |
[o:2021.03,i:2021.02,ocurrARBal:25,icurrRev:20,Country:IN,icurrAR:0,revSUMXP:40,revSUMX:60,pleft:-15,cleft:-35,multiplier:-0.75,result:0] |
[o:2021.03,i:2021.01,ocurrARBal:25,icurrRev:50,Country:IN,icurrAR:0,revSUMXP:60,revSUMX:110,pleft:-35,cleft:-85,multiplier:-0.7,result:0]
[o:2021.03,i:2021.03,ocurrARBal:25,icurrRev:40,Country:IN,icurrAR:25,revSUMXP:,revSUMX:40,pleft:25,cleft:-15,multiplier:0.625,result:18.75] |
[o:2021.03,i:2021.02,ocurrARBal:25,icurrRev:20,Country:IN,icurrAR:0,revSUMXP:40,revSUMX:60,pleft:-15,cleft:-35,multiplier:-0.75,result:0] |
[o:2021.03,i:2021.01,ocurrARBal:25,icurrRev:50,Country:IN,icurrAR:0,revSUMXP:60,revSUMX:110,pleft:-35,cleft:-85,multiplier:-0.7,result:0]

 

Please let me know where i am making mistake. It looks to me that the filter is ignoring the country values.

 

 

SUMM_DSO (CB) = SUMX(
// outer loop
VALUES(DSO_AR_SALES),
VAR daysInMonth = 30
VAR ocurrPeriod = DSO_AR_SALES[DATE]
VAR Country = DSO_AR_SALES[Country]
VAR ocurrARBal = CALCULATE(DSO_AR_SALES[SummaryAR],FILTER(ALL(DSO_AR_SALES), DSO_AR_SALES[DATE]=ocurrPeriod))
 
RETURN
SUMX(
// inner loop
FILTER(ALL(DSO_AR_SALES[Date]), DSO_AR_SALES[DATE]<=ocurrPeriod && DSO_AR_SALES[Country] = Country),
VAR icurrPeriod = DSO_AR_SALES[DATE]
VAR icurrRev = CALCULATE(DSO_AR_SALES[SummarySL] , FILTER(ALL(DSO_AR_SALES), DSO_AR_SALES[DATE]=icurrPeriod && DSO_AR_SALES[Country] = Country))

VAR revSUMX =
CALCULATE(
SUMX(DSO_AR_SALES, DSO_AR_SALES[SummarySL]),
FILTER(ALL(DSO_AR_SALES), DSO_AR_SALES[DATE] >= icurrPeriod && DSO_AR_SALES[DATE]<=ocurrPeriod && DSO_AR_SALES[Country] = Country))

VAR revSUMXP =
CALCULATE(
SUMX(DSO_AR_SALES, DSO_AR_SALES[SummarySL]),
FILTER(ALL(DSO_AR_SALES), DSO_AR_SALES[DATE] > icurrPeriod && DSO_AR_SALES[DATE]<=ocurrPeriod && DSO_AR_SALES[Country] = Country)
)

VAR pleft = ocurrARBal - revSUMXP
VAR cleft = ocurrARBal - revSUMX    
VAR multiplier =
IF (ocurrARBal > revSUMX, 1,
Divide(pleft, icurrRev)
)
 
VAR result = IF (multiplier > 0 || cleft > 0,
daysInMonth * multiplier,
0)
Return
Result
)
)
 
 

 

1 REPLY 1
v-yalanwu-msft
Community Support
Community Support

Hi, @RAVISHhcl ;

I'm sorry that I couldn't determine the cause of the [SummaryAR] and [SummarySL] formula based on the information you gave me. The formula was not clear, so the test didn't go well. Second, can you share the results you want to export? Or provide a simple sample after removing sensitive information?

vyalanwumsft_0-1638409894382.png

Try it?

Measure = SUMX(FILTER(ALL('DSO_AR_SALES'),[Country]=MAX('DSO_AR_SALES'[Country])&&[Customer]=MAX('DSO_AR_SALES'[Customer])),[DsO(CB)])

The final output is shown below:

vyalanwumsft_1-1638409947827.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors