calculate and If function in measure

[Anonymous]

Hi,

I have data in following format. 

I need to calculate average balances based on total days in a month except that calculation is different for each source. 

source	date	balance	weekday
a	05-Nov	100	Monday
b	05-Nov	250	Monday
a	04-Nov	150	Sunday
b	04-Nov	200	Sunday
a	03-Nov	120	Saturday
b	03-Nov	200	saturday
a	02-Nov	130	Friday
b	02-Nov	200	Friday
a	01-Nov	160	Thursday
b	01-Nov	230	Thursday
a	31-Oct	140	Wednesday
b	31-Oct	220	Wednesday

If source = a, sum(balance)/total days in the month

If source = b, sum(balance)/total woking days in a month

I have created a date table that gives me working days (1) and non working days(0).

I tried using calclulate and if function together but that doesnt seem to be working. 

Each source has a subdivision. I need to show my output in the following manner (Numbers are random) for a particular month selected on a slicer. 

	average balance
a.1	100
a.2	200
a.3	300
b.1	150
b.2	250
b.3	300

Can someone help. Thanks in Advance!

[Anonymous]

Let's see if this can get you in the right direction.  Going off the data in your first table, created a calculated column to Flag Weekends ( 1 = weekend, 0 = weekday):

Then create a measure for the balance total.  I always use explicit measures, never implicit (i.e. just checking the box next to the balance):

 

Now, we need to count the Working Days and Weekend Days.  The following is basic and will need some further refinement when used with more data, but the core of the idea is there:

 

Total Days is just the sum of those above two measures:

Now need to know when to use the Total Working Days Measure as the denominator, or the Total Days measure.  Since we only have two choices (A or B ) nested ifs would work, but they are clunky and if you start getting more choices even more so.  I opted to use SWITCH.

Then from there the final piece is to use the DIVIDE function:

Final Table ( the names of the measures should be something smaller, but left them more descriptive) and wouldn't put them all on a table

I think that should get you in the right direction.  With the two COUNTROWS measures, there will be some more considerations when you have more data ( i.e. only count number of days up to a specific point in a specific month) but those are pretty easy to add in.

[Anonymous]

Hi @Anonymous,

Thanks this makes sense, but i have something more to add to. Each source has data from differernt clients like thi:

source	ClientID	date	balance	weekday	Isweekend
a	C1	05-Nov	10	Monday	0
a	C2	05-Nov	20	Monday	0
a	C3	05-Nov	30	Monday	0
a	C4	05-Nov	40	Monday	0
b	D1	05-Nov	50	Monday	0
b	D2	05-Nov	60	Monday	0
b	D3	05-Nov	70	Monday	0
b	D4	05-Nov	80	Monday	0
a	C1	04-Nov	20	Sunday	1
a	C2	04-Nov	30	Sunday	1
a	C3	04-Nov	40	Sunday	1
b	D1	04-Nov	60	Sunday	1
b	D2	04-Nov	70	Sunday	1
b	D3	04-Nov	80	Sunday	1

So, for calculating weekends and weekdays it is counting each client for count rows function. For example:

For 5th Nov: first 8 rows should be counted as 1 for each source, however it is counting as 4 each. Each source has differnt rows everyday due to different number of clients. How to avoid this error?

[v-piga-msft]

Hi @Anonymous,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share your desired output so that we could help further on it.

Best  Regards,

Cherry

[Anonymous · Accepted solution]

Thinking using DISTINCTCOUNT instead of COUNTROWS will work ('Calc and IF' is the name of the table):

CountRows Weekday = 
CALCULATE(
    DISTINCTCOUNT('Calc and IF'[date]),
    'Calc and IF'[Isweekend]=0)

CountRows Weekend = 
CALCULATE(
 DISTINCTCOUNT('Calc and IF'[date]),
 'Calc and IF'[Isweekend]=1)

It will give you the disctinct count based on the current filter context.  So if you just have Source in the current filter context, will get 1 for each source A and B, since each source has a weekday and a weekend record

Samething happens when you add in Client ID, except that aC4 and bD4 only have a count (1) for Weekeday and blank for Weekend since the filter context is saying let's get the distinct count of source = a, Client = C4, and IsWeekend=  1, which doesnt exist: