Forum Discussion
Distinct Count
Hi Pedro
Start afresh and ignore the previous DAX measures for WOI < 2. Add 2 new measures:
WOI < 2 (hidden) = if([WOI (8W)] > 0 && [WOI (8W)] < 2,1,0)
WOI < 2 = SUMX ( VALUES ( SO_INV_LOJA[Nome da Loja] ), CALCULATE ( [WOI < 2 (hidden)] ) )
For some reason I can't add a screen shot so will try doing that in another reply.
The hidden measure calculates WOI but only in the context where you have the Nome da Loja. If you look at the total it shows as zero because the total WOIis 8.51.
The visible WOI < 2 measure shows the total correctly because it is forcing the calculation to be applied at a grouped level.
I found the answer here: https://brentgreenwood.blogspot.com/2012/12/dax-groupers-summarize-and-aggxvalues.html
Thanks
Brian
screen shot:
Oh, sorry.. the right answer is: (like the colors)
| store | woi |
| a | 1.5 |
| b | 1.7 |
| c | 2.3 |
| d | 2.5 |
| e | 3 |
| f | 5 |
| WOI >2 | WOI 2-4 | WOI<4 | ||
| 2 | 3 | 1 |
Im using this DAX, but the result is wrong:
Hi Anonymous
I don't understand your calculation?
Based on my understanding, the values satisfy the condition as below
| store | woi | WOI >2 | WOI 2-4 | WOI<4 |
| a | 1.5 | Y | ||
| b | 1.7 | Y | ||
| c | 2.3 | Y | Y | Y |
| d | 2.5 | Y | Y | Y |
| e | 3 | Y | Y | Y |
| f | 5 | Y |
Maggie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
There are different ways of doing this depending on how much data you have and that effects performance, or what you want to do with visuals.
You can either add a new calculated column to the table that reflects the categories so would have the following values “WOI < 2”, “WOI 2-4” and “WOI > 4”. The calculation would be a set of nested Ifs. Then have a measure that does a COUNTROWS on the table. In the visual you use the new category and new measure.
The other way of doing this is to create a new measure for each of the categories. The “WOI < 2” measure would be:
WOI < 2 = calculate(COUNTROWS( FILTER(sheet1,Sheet1[WOI]<2)))
Note the measure above can be achieved with an IF statement but best practice is to use FILTER as this is much faster. For small datasets if may not matter but larger datasets it will
Hope this helps, any questions just shout!
Hi bpsearle, tks for help.
I think that we are getting there.
The exemplo above is what I am trying to do with WOI measure.
the correct answer for the DAX is: WOI <2 = 2 rows
I tried to use the DAX that u showed to me but the answer was 813. Am I using a wrong period?
WOI is a calculated measure formula that means = current inventory / avg sell out 8 weeks. It's not a column on the DB
On my DB I have granularity at product level but I need the answer at store level. I think that's impacting our results.
Thanks again.
Pedro.
If your underlying data has a lower grain then that would explain the 813, assuming that there are 813 rows that satisfy the measure.
Its difficult to say if its correct without understanding the detailed rows and what the DAX is in “WOI (8W)”. For this reason, I’m not 100% sure how using “WOI (8W)” will work when we use it for the filter.
To group the detail data to the level of “store” try this
WOI < 2 =
calculate(
COUNTROWS(
SUMMARIZE(
FILTER(YourTable, YourTable[WOI (8W)]<2),
YourTable[StoreColumnName]
)
)
)
See if that works…