Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Distinct Count

Hi guys,   I'm having problems with a distinct count measure on card visualization on Power BI. Could you help me please?   I have a calculated measure on a matrix and I need to have a card to co...
Need Help
  • bpsearle's avatar
    bpsearle
    6 years ago

    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

  • bpsearle's avatar
    bpsearle
    6 years ago

    screen shot:

bpsearle's avatar
bpsearle
Resolver II
6 years ago

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!

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.

  • bpsearle's avatar
    bpsearle
    Resolver II
    6 years ago

    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…

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    It didn't work 😞

     

  • bpsearle's avatar
    bpsearle
    Resolver II
    6 years ago

    Is it possible to post a cut down pbix file with any sensitive data removed, just the data set in question and the visual?

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    In your example, 

    the line (43/22) should equal to 1.95 accurated to 2 decimal points,

    in your example, you keep it to 1 decimal points, so the value is 2.0,

    to get correct result, we should create another measure to round up the [WOI] to the 1 decimal points.

    also, we need modify your [woi] measure.

    create measures as below

    Measure woi =
DIVIDE (
    CALCULATE (
        SUM ( Sheet1[current inv] ),
        FILTER ( ALLSELECTED ( Sheet1 ), Sheet1[store] = MAX ( Sheet1[store] ) )
    ),
    CALCULATE (
        SUM ( Sheet1[avg] ),
        FILTER ( ALLSELECTED ( Sheet1 ), Sheet1[store] = MAX ( Sheet1[store] ) )
    )
)

round up = ROUNDUP([Measure woi],1)

<2 = CALCULATE(DISTINCTCOUNT(Sheet1[store]),FILTER(ALLSELECTED(Sheet1),Sheet1[round up]<2))

>4 = CALCULATE(DISTINCTCOUNT(Sheet1[store]),FILTER(ALLSELECTED(Sheet1),Sheet1[round up]>4))

2~4 = CALCULATE(DISTINCTCOUNT(Sheet1[store]),FILTER(ALLSELECTED(Sheet1),Sheet1[round up]>=2&&Sheet1[round up]<=4))

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi v-juanli-msft  , thank u so much, I think that we are close now..

     

    One more question. How can I calculate the SUM of Current INV considering that this field is a calculated one?

     

     

     

    Thanks again,

    Pedro.

  • bpsearle's avatar
    bpsearle
    Resolver II
    6 years ago

    Hi Pedro

    You shouldn't need to wrap sum around the measure for the expression in calculate.

    I'm going to have a quick look at mocking up the aggregate problem this morning and see if I can get an answer for that too!

    Thanks,

    Brian