Sumx multipling total with number of rows

[salman_ali]

Hello 

this is my measure 

here the measure calculate count of rows in orders pending table, where column will_call_tank = 'y'

this formula works when hasonevalue, however when I sumx for rollup value it multiplies the result by count of rows.

ths is the output I am getting

here DELcenter (Morden, Brandon, Winnpeg) are providing current values as 2, 6, and 9 respetively, however the rollup at the market level 100-Manitoba, ought to be 27, not 81.  The sumx is taking 27 and mulitplying by 3 (rows os del center) to give me 81.

why is this happening?

[AlB]

@salman_ali 

Where is the table you showed earlier in the report you've shared? I cannot find it 🤔

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[salman_ali]

@AlB 

I have pasted snip of both the table and measure.  Do you see it in the file?

[salman_ali]

that table is in the shifting tab, top right 

it is the table on top of "COUnt of Pending orders by Forecast"

[AlB]

@salman_ali 

Ok, i found it. Unfortunately I cannot make changes to the report to try a couple of things because it has a live connection. and i don't have access to that connection. So I'm  abit blind here. Try:

Measure =
SUMX (
    VALUES ( F0006s[Description] ),
    CALCULATE (
        COUNTROWS ( OrdersPending ),
        FILTER ( OrdersPending, OrdersPending[will_call_tank] = "y" )
    )
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[salman_ali]

@AlB  yeah thanks for trying, I tried the above measure, same result.  Not sure why this is happening, but I think your first explanation sounded correct 

[AlB · Accepted solution]

@salman_ali 

Try

Measure =
SUMX (
    VALUES ( F0006s[Description] ),
    CALCULATE ( COUNTROWS ( OrdersPending ), OrdersPending[will_call_tank] = "y" )
)

or

currentwcorders =
SUMX (
    VALUES ( OrdersPending[DelCenter] ),
    CALCULATE ( COUNTROWS ( OrdersPending ), OrdersPending[will_call_tank] = "y" )
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[salman_ali]

wow!! the first one worked!!!! thank you so much !!!

[AlB]

Hi @salman_ali 

Because you are assigning the result to a variable. At the subtotal row, when you create the variable there is no row context and the filter context is 100-Manitoba. For that, the number of rows with will_call_tank = 'y' is 2+6+19 = 27, which is stored in the variable. Then, you run the SUMX( ) over VALUES( .Del Center). There are 3 rows in that VALUES(). For each row,  you assign the value of the variable. So with the SUMX you have 3x that value, i.e., 3x27=81

There is row context within the SUMX but context transition is not being triggered. Plus remember that variables are immutable in DAX; their value will never change after creation. In that sense they act more as constants than as variables (as typically known in other programming languages)

You can simplify the measure:

Measure V2 = 
SUMX( VALUES( OrdersPending[DelCenter]), CALCULATE(...........) )

where the CALCULATE is what you are assigning to the VAR

Next time, please paste the code in text so that it can be copied and modified easily

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[salman_ali]

Thanks @AlB  i tried using this measure

currentwcorders = SUMX(values(OrdersPending[DelCenter]), CALCULATE(COUNTROWS(OrdersPending),filter(OrdersPending,OrdersPending[will_call_tank]="y")))

but output is still the same 

[AlB]

@salman_ali 

If the results for the storage rows are correct, the result for the subtotal should be too. I'd need to see the pbix. Can you share it?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[salman_ali]

@AlB  i don't know how to send sample pbix, but i am sending you the wetransfer link 

pbix file  

hopefully this works.  this is the full pbix model.  let me know if it is too confusing