Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear All,
I have the following database:
from which I got the following table:
The measures involved are the followings:
1) COUNTROWS = COUNTROWS('Table')
2) DISTINCTCOUNT = DISTINCTCOUNT('Table'[RowNum])
3) QUANTITY = IF([COUNTROWS]<>[DISTINCTCOUNT],SUMX('Table','Table'[Q.ty]/[COUNTROWS]),SUMX('Table','Table'[Q.ty]))
The latter measure isn't working properly. If you look at the product "Kiwi" in the visualization, the quantity is "2", but it should be "1" according to the IF statement...
Is there a way to solve this? I attach a sample
Thank You
Solved! Go to Solution.
Hey @gianmarco ,
that's actually not that easy. In this case you have to create an internal table with all the conditions and then calculate the values based on that one.
The following measure should work:
Quantity NEW =
VAR vSummTable =
ADDCOLUMNS(
SUMMARIZE(
'Table',
'Table'[ProdName],
'Table'[Q.ty],
'Table'[RowNum]
),
"@Countrows", [COUNTROWS],
"@Distinctcount", [DISTINCTCOUNT]
)
VAR vSummTableQty =
ADDCOLUMNS(
vSummTable,
"@Quantity",
IF( [@Countrows] <> [@Distinctcount], CALCULATE( SUM( 'Table'[Q.ty] ) ) / [@Countrows], 'Table'[Q.ty] )
)
RETURN
SUMX(
vSummTableQty,
[@Quantity]
)
And I think the result is how you want it:
Hi @gianmarco ,
I believe this is due to the fact that you've used SUMX. SUMX is an iterator function which will sum the result of each row evaluation.
In your example, it does the following for Kiwi:
Table Row 2: 1 (Q.ty) divide 1 (COUNTROWS on that one row that is being evaluated) = 1
Table Row 3: 1 (Q.ty) divide 1 (COUNTROWS on that one row that is being evaluated) = 1
SUMX does 1 + 1 = 2
Try this measure instead:
QUANTITY =
IF(
[COUNTROWS] <> [DISTINCTCOUNT],
DIVIDE(SUM('Table'[Q.ty]), [COUNTROWS], 0),
SUM('Table'[Q.ty])
)
Pete
Proud to be a Datanaut!
Dear
Thank you a lot for your kind answers. All three are great but still can't get what I really need.
Thanks to you I could do the following:
My problem now is the Total QUANTITY. I understand the 1,67 but I need it to be 4,00.
Now you may ask what I need to get as a result... The fact is that I have some duplication/triplication of rows in my db because some products have different expiry dates. So I need to to maintain those rows but I also need NOT to sum the quantity of those rows... 😕
Hey @gianmarco ,
that's actually not that easy. In this case you have to create an internal table with all the conditions and then calculate the values based on that one.
The following measure should work:
Quantity NEW =
VAR vSummTable =
ADDCOLUMNS(
SUMMARIZE(
'Table',
'Table'[ProdName],
'Table'[Q.ty],
'Table'[RowNum]
),
"@Countrows", [COUNTROWS],
"@Distinctcount", [DISTINCTCOUNT]
)
VAR vSummTableQty =
ADDCOLUMNS(
vSummTable,
"@Quantity",
IF( [@Countrows] <> [@Distinctcount], CALCULATE( SUM( 'Table'[Q.ty] ) ) / [@Countrows], 'Table'[Q.ty] )
)
RETURN
SUMX(
vSummTableQty,
[@Quantity]
)
And I think the result is how you want it:
Hey @gianmarco ,
acutally the measure is working properly. I just think you want something else.
SUMX is an iterator, what means it goes through the table line by line, calculates the expression for each line and at the end is building a SUM of the expressions for each line.
For the kiwi, it takes the first line and calculates [Q.ty] (what is 1) divided by [COUNTROWS] (what is 1). So the result is 1.
Then it will do the same for the next row, the result is again 1.
Afterwards SUMX will sum up the two results and as 1 + 1 = 2 you get the correct result.
I think what you want is:
SUM( 'Table'[Q.ty]) / [COUNTROWS]
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |