Forum Discussion

micang's avatar
micang
Regular Visitor
1 year ago
Solved

% with static denominator

Hi All,   I have a simply power bi report.   It consistes of a table and 6 slicers.   5 of the slicers are simple dropdowns of columns (purely to filter the table) 1 of the slicers is a date s...
  • sjoerdvn's avatar
    sjoerdvn
    1 year ago

    Try VALUES instead of KEEPFILTER

    Referral % = 
VAR CurrentCount = DISTINCTCOUNT(Data[referral number])
VAR BaseCount =
    CALCULATE(
        DISTINCTCOUNT(Data[referral number]),
        ALL(Data),
        VALUES(Data[Date])
    )
RETURN
DIVIDE(CurrentCount, BaseCount)
  • v-sdhruv's avatar
    v-sdhruv
    1 year ago

    Hi micang ,
    You can try this approach after using the measure shared by Sahir_Maharaj 
     Create a Filtered measure-
     FilteredReferralCount =
    DISTINCTCOUNT('Data'[Referral Number])
    This will respect all the slicers.
    Then create the final measure-

    ReferralSharePct =
    DIVIDE([FilteredReferralCount], [BaseReferralCount], 0)
    Hope this helps!
    If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
    Thank You

micang's avatar
micang
Regular Visitor
1 year ago

Hi Sahir_Maharaj 

This is so close, appreciate your time - the calculations are correct, but only if there is no filtering on the date slicer.

Example, lets say the total referral number distinct count of the whole dataset is 1000, so what it's doing now, when other slicers are selected, its using 1000 as the denominator, irrespective what the date slicer is on. So if I have the data slicer not filtered and have no other slicers filterered, then the % is 100, whichis correct (1000/1000). If I then slicer on country, and country (USA) is 50, then % shows  5, which is correct (50/1000) - but now, as soon as i use the data slicer and lets say i choose 1 month and, its still using the denominator of 1000 and not the distinct count of that month.

Thank you so much for your effort, this is he closest I have gotten after hours and hours of trying.

 

Thanks

micang