Measure to calculate Percentages - Usage - ALLEXCEPT()

[vinaydavid]

Hi Experts,

I have been trying to get the percentage values right for my calculations.

Need a bit of code help in getting the right percentages.

Here is the scenario.

Name	Project	Leaves	Number of days	Hours	Percentage (Expected)
AAA	Red		23	97.92	0.71
AAA	Blue		23	41.83	0.30
BBB		15.2	23	15.20	0.00
BBB	Red		23	136.80	1.11
CCC	Red		23	89.00	0.64
CCC	Green		23	4.00	0.03
DDD		24	23	24.00	0.00

Percentage = 
VAR __DAYS =
    CALCULATE ( SUM ( Testing[Number of days] ) )
VAR __LEAVES =
    CALCULATE ( SUM ( Testing[Leaves] ) )
RETURN
    DIVIDE (
        CALCULATE (
            SUM ( Testing[Hours] ),
            ALLEXCEPT ( Testing, Testing[Name], Testing[Project] )
        ),
        ( __DAYS * 6 ) - __LEAVES,
        0
    )

The above formula doesn't seem to be deducting the leaves from the denominator.

Eg: For User BBB, I want the calculation to be  (136.8)/ (23 * 6) - (15.2) = 1.11

      But the above formula doesn't help, resulting in 136.8 / (23 * 6) => 0.99 (thats incorrect)

Note: Leave hours doesn't have project details.

Here is a sample PBIX.

https://1drv.ms/u/s!Ao2V7OnnFNS_hR5Ofj6eCl7rn2uL?e=c3nGzt 

[danextian]

Hi @vinaydavid 

You have one more lines for each project name. The second of BBB doesn't have leaves so it is not being included in the calculation. You can try this formula: 

Percentage_ =
VAR __DAYS =
    SUM ( Testing[Number of days] ) * 6
VAR __LEAVES =
    CALCULATE ( SUM ( Testing[Leaves] ), ALLEXCEPT ( Testing, Testing[Name] ) )
VAR __HOURS =
    SUM ( Testing[Hours] )
RETURN
    IF (
        SELECTEDVALUE ( Testing[Project] ) = BLANK (),
        0,
        DIVIDE ( __HOURS, __DAYS - __LEAVES )
    )

[vinaydavid]

Thanks @danextian  for the response.

The formula is working perfectly fine. But its picking all the leave values for the 'Name'. I want to restrict it by a date filter. (Sorry not informed earlier)

Apologies as its a test data, I missed to include the date filter.

The report has a date selection (From & to dates). 

Something like this. (not in the sample pbix)

FILTER ( Testing,
         Testing[LogDate] <= __END_QUERY
         && Testing[LogDate] >= __START_QUERY
         )

How can I incorporate to get the '__LEAVES' variable values as 15.20 on a Name level by using the above filter.

I tried to put the above filter, but then it give me '15.20' (empty project) '0' (with project) for BBB.

Thanks!

[danextian]

Change this line from

VAR __LEAVES =
    CALCULATE ( SUM ( Testing[Leaves] ), ALLEXCEPT ( Testing, Testing[Name] ) )

to

VAR __LEAVES =
    CALCULATE ( SUM ( Testing[Leaves] ), ALLEXCEPT ( Testing, Testing[Name], Testing[Date] ) )

This should keep the total sum of leaves by each unique name but still allow the breakdown by date.

[vinaydavid]

Thanks @danextian,

I understand, where you are coming from.

But I guess, calculate function works in a way to pick all the dates for each name (in this case BBB), and it doesn't have the impact of the Slicer.(Correct me if I am wrong).

So when I used your code, its displaying more hours (ie., all the hours from the database for BBB user).

Also tested, Filter() to restrict the data in calculate function, but then it work on row context to display the correct value only for 'Blank' Project. And corresponding row with a project for name BBB has hours as 'Blank'.

[v-chuncz-msft · Accepted solution]

@vinaydavid 

You may simply use ALLSELECTED.

VAR __LEAVES =
    CALCULATE ( SUM ( Testing[Leaves] ), ALLSELECTED ( Testing[Project] ) )