Distinct Count

[Anonymous]

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 count the number of occurrences higher than a certain value.

Thanks,

Pedro.

[bpsearle · Accepted solution]

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

[v-juanli-msft]

Hi @Anonymous 

I create measures to refer to measure [current inv] and [avg] in your example.

assume I have two measure which use columns as fields.

then use the two measures in another measure to calculate the "woi"

Measure aveg = SUM(Sheet1[avg])

Measure current inv = SUM(Sheet1[current inv])

Measure woi =
SUMX (
    FILTER ( ALLSELECTED ( Sheet1 ), Sheet1[store] = MAX ( Sheet1[store] ) ),
    [Measure current inv]
)
    / SUMX (
        FILTER ( ALLSELECTED ( Sheet1 ), Sheet1[store] = MAX ( Sheet1[store] ) ),
        [Measure aveg]
    )

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.

[bpsearle · Accepted solution]

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

[Anonymous]

Hi @bpsearle It worked! 🙂

Thank u so much. I really appreciate that.

Pedro.

[bpsearle]

I'm really pleased I could help - high 5s all round

[bpsearle · Accepted solution]

screen shot:

[Anonymous]

Hard to say excalty what is needed with that limited information. Can you provide a sample of your data and what you'd expect the result to be?

[Anonymous]

Hi,

I can give you an example:

*WOI - Weeks of Inventory

store	woi
a	1.5
b	1.7
c	2.3
d	2.5
e	3
f	5

I need to create a conditional/dax on a card that shows me this: 

WOI >2		WOI 2-4		WOI<2
2		3		1

How should I do this distinct counting considering that my WOI field is a calculated measure?

Thanks.,

Pedro.

[Anonymous]

Are those displayed results be what you would expect from the sample data you displayed?

I believe  the expected results are shown below

WOI >2		WOI 2-4		WOI<2
4		3		2

[Anonymous]

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: 

teste = CALCULATE(DISTINCTCOUNT(SO_INV_LOJA[Nome da Loja]);FILTER(SO_INV_LOJA;[WOI (8W)]<2))

[Ross_PBI]

@Anonymous  You have your greater than > and less than < symbols the wrong way round which is why @v-juanli-msft  is confused. His interpretation of what the results should look like is correct based on how you present the <> symbols

[Anonymous]

Hi @Ross_PBI  and @v-juanli-msft , you guys are right. I switched the symbols.

Could you help me based on my answer to @bpsearle ? I exemplified better what I am trying to do.

Thank u so much.

Pedro.

[v-juanli-msft]

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

Please clear me so we can move to next step.

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.

[bpsearle]

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]

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.

[v-juanli-msft]

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]

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]

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

[bpsearle]

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]

It didn't work 😞

[bpsearle]

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