Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculating distinct shares with Date Table?

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

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

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

     

     

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous,

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago
      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Anonymous,

        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