Power BI complex total problem

Question

I have a problem with the summing of totals in a power BI matrix. I can normally sort these out but this one has got me pulling hair out.

VAR UnSelectedProduct = ( [Total Impact Products Unselected] - [Sales RS Products Unselected] )
VAR UnselectedProductSelectedResellers = IF(ISBLANK([Sales Reseller allselectedPROD]),BLANK(),UnSelectedProduct)

RETURN
IF(HASONEVALUE(dReseller[SellerName]),
UnselectedProductSelectedResellers,
SUMX(FILTER(VALUES(dReseller[SellerName]),[Sales Reseller allselectedPROD]<>BLANK()),
UnselectedProduct)
)

The total value should be £1,064,475 but it returns £567,671,285 !! I cannot work out what this number is.

The data is taken from the Microsoft Adventure Works database. he scenario i am trying to create is some pricing analysis taken from a YouTube example by Sam McKay from Enterprise DNA called 'Complex 'What If' Analysis Example In Power BI Using DAX'

What i am trying to do is make a small adjustment to what he did by only putting the demand increase on all other products on the resellers that sell the selected products.

Please Help!! I can normally sort these problems but this has me stumped, i have tried multipul different dax formulas

Answer 1

How about a hint: the mysterious value should be divisible by the number of rows returned by the Filter call :)

Answer 2

Hi AlexeiStoyanovsky,

Big thanks for your reply.

You were very close :)

I used

VAR Rowcount = CALCULATE(COUNTROWS(dReseller),FILTER(dReseller,[Sales Reseller allselectedPROD]<>BLANK()))

The DAX is now this :

IF(HASONEVALUE(dReseller[SellerName]),
UnselectedProductSelectedResellers,
SUMX(FILTER(VALUES(dReseller[SellerName]),[Sales Reseller allselectedPROD]<>BLANK()),
UnselectedProduct)/RowCount

It returned 253 rows which brought me to the value £2,243,760 (i knew this number) it is the subtraction of total values of the columns in the VAR UnselectedProducts.

The DAX is now this :

IF(HASONEVALUE(dReseller[SellerName]),
UnselectedProductSelectedResellers,
SUMX(FILTER(VALUES(dReseller[SellerName]),[Sales Reseller allselectedPROD]<>BLANK()),
UnselectedProduct)/RowCount

However these column totals are incorrect with the same problem, when the formula gets to the total it subtracts the incorrect totals.

I need to sum the values in the column above as they are correct and when exported to excel they summed up to £1,064,475 which is correct from my excel checks. In the past with similar problems i have used the following DAX to sum the total:

SUMX(SUMMARIZE(VALUES(dReseller[SellerName]),dReseller[SellerName]),UnselectedProductSelectedResellers)

I have tried this but it returns £1,568,388,256.42 !!! not getting that !

Why is this not working ? Please help a little more

Answer 3

Hi AlexeiStoyanovsky,

Your a certified genius !!! you fixed it and something i should have got i suppose,

I'm not an I.T. guy i work in Finance more as an analyst these days and i am really trying to develop the FP&A (Financila Planning and Analysis) side of my job.

I have had similar troubles with variables before and should have tried that.

PS Between this post and your last post somebody replied and redirected me to a Power BI forum saying this was not the place. I have reposted there, I'll need to take down now.

So again Many thanks for your help !