Forum Discussion

Nath_nath's avatar
Nath_nath
Frequent Visitor
4 years ago
Solved

Creating a distinct count measure based on a total

Dear fellow PBI enthusiasts,

I am having more than expected trouble finishing this measure. I would like to calculate the unique number of cases where total case amount (irregards used slicers) is above a certain number. In fact I would like to make a measure which could create the most right (blue) column. 

 


Note: simply using below formula will not work as I need total per case. Below formula would resemble the result in the most right yellow column.

 

Count = Calculate ( distinctcount(fact'[cases]) , fact'[minutes] > 100)

 

Also this should work in a power bi dataset using a start model where multiple other dimensions are used. If anyone would be so kind to help, just assume that for each of the dimensionfields seeing in above example, in reality a seperate dimension table exists.


Thank you so much!

 

 

Need Help
  • DataInsights's avatar
    DataInsights
    4 years ago

    Nath_nath,

     

    Try these measures. The variable vCaseTotals is a virtual table that contains the total minutes for each case, which is then filtered for "> 100". Then, the virtual table of cases > 100 is used as a CALCULATE filter.

     

    Total Minutes = SUM ('fact'[Minutes] )
    Distinct Count Minutes > 100 = 
VAR vCaseTotals =
    ADDCOLUMNS (
        VALUES ( 'fact'[Case] ),
        "@Minutes", CALCULATE ( [Total Minutes], ALLEXCEPT ( 'fact', 'fact'[Case] ) )
    )
VAR vCasesOver100 =
    FILTER ( vCaseTotals, [@Minutes] > 100 )
VAR vResult =
    CALCULATE ( DISTINCTCOUNT ( 'fact'[Case] ), vCasesOver100 )
RETURN
    vResult

     

     

1 Reply

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    4 years ago

    Nath_nath,

     

    Try these measures. The variable vCaseTotals is a virtual table that contains the total minutes for each case, which is then filtered for "> 100". Then, the virtual table of cases > 100 is used as a CALCULATE filter.

     

    Total Minutes = SUM ('fact'[Minutes] )
    Distinct Count Minutes > 100 = 
VAR vCaseTotals =
    ADDCOLUMNS (
        VALUES ( 'fact'[Case] ),
        "@Minutes", CALCULATE ( [Total Minutes], ALLEXCEPT ( 'fact', 'fact'[Case] ) )
    )
VAR vCasesOver100 =
    FILTER ( vCaseTotals, [@Minutes] > 100 )
VAR vResult =
    CALCULATE ( DISTINCTCOUNT ( 'fact'[Case] ), vCasesOver100 )
RETURN
    vResult