Calculating distinct shares with Date Table?

[PeeJii]

Hi,

I need to get distinct share of Orders that fit the selected time frame. I have date filter linked to date table with dates.

This gives me The Total rows:

And this one gives me ones with order:

So the share is this

Share = Calculate([Distinct_Suunnittelytyo_ROWS] / [Distinct_ROWS])

It does not take selected time frame in the count though.  

I wish I could you this as a filter -> OsuusAikaJaksolleLuotu = 1

I just don't know how combine all these. Page consist of three different graph all filtered with different time filter so a page level filter is out of the question.

So I'm looking for a card with share that changes with date slicer. Anyone?

Cheers

PeeJii

[v-shex-msft]

Hi @PeeJii,

I think you need to use the unconnected data table as source of filter/slicer, then you can try or use following measure to calculate the result based on filter selection:

formula =
VAR selection =
    ALLSELECTED ( DateTable[Date] )
RETURN
    CALCULATE (
        DIVIDE (
            DISTINCTCOUNT ( Table[ID] ),
            CALCULATE ( DISTINCTCOUNT ( Table[ID] ), Table[Status] = 1 )
        ),
        FILTER ( ALLSELECTED ( Table ), Table[Date] IN selection )
    )

Regards,

Xiaoxin Sheng

[PeeJii]

formula = 
VAR selection =
    ALLSELECTED ( 'Dates (MOC)'[Date] )
RETURN
 CALCULATE (
   DIVIDE (
     DISTINCTCOUNT ( 'MOC Dokumentit'[MF ID] ),
     CALCULATE ( DISTINCTCOUNT ( 'MOC Dokumentit'[MF ID] ), 'MOC Dokumentit'[On Tilaus] = 1 )
     ),
        FILTER ( ALLSELECTED ( 'MOC Dokumentit' ), 'MOC Dokumentit'[Luotu] IN selection )
    )

Thanks. I tried yours, but don't get the right number. To me the measure looks right.

[v-shex-msft]

Hi @PeeJii,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[PeeJii · Accepted solution]

I found a solution. This is how I get desired result: