Need help with DAX - aggregating using share% in one of the steps

[Anonymous]

Hi all,

need your help with figuring out how to build a measure - I have to adjust working days calendar based on share% calculated from sales figures, and it needs to change based on filters selected. Here's how these steps work in my mind:

share% = amountItem / amountTotal

wd_adjusted = share% * workingDay

Dataset consists of 4 dimensional fields and 1 measure:

dimension1, compcode, caldate, [working days], amount

If I recreate the calculation in Excel, it looks like this:

Step 1 - calculate share%

Step 2 – join it to the initial dataset (this step is probably not needed in PBI):

Step 3 – agreggate “working days adjusted” based on caldate:

PBIX

Here's my model:

What I tried so far:

step 1 - find item and total

amount_item = CALCULATE(SUM('fact'[amount]), ALLEXCEPT(dim_comp, dim_comp[CompanyCodeKey]), ALLEXCEPT(dim_dim1, dim_dim1[dimension1]))

amount_total = CALCULATE(SUM('fact'[amount]), ALLSELECTED(dim_comp[CompanyCodeKey]), ALLSELECTED(dim_dim1[dimension1]))

step 2 - calculate share%

share = DIVIDE([amount_item], [amount_total])

step 3 calculate wd adjusted:

wd_adjusted = SUMX('fact', [share] * 'fact'[working days])

I tried to adjust formulae with ALL, ALLSELECTED and ALLEXCEPT, but with no real success.

PROBLEM: it seems that share% * "working days" keeps summing up days even though I used SUMX.

LINKS:

pbix: https://1drv.ms/u/s!AtNDPFIXfkrDai_gLYXlzudU1c0?e=0UNv2V 

excel with calculation steps: https://1drv.ms/x/s!AtNDPFIXfkrDaWBYevbK-BojBbU?e=CbwSqw 

I know I'm doing something very wrong with the way I use share calc. measure, but for the life of me I cannot figure what - any help or advice would be immensely appreciated!

[v-yueyunzh-msft]

Hi , @Anonymous 

For your need , Here are the steps you can refer to :
(1)We need to click "New Column" to create a calculated column:

Woring days adjust = var _caldate = 'fact'[caldate]
var _sum =SUMX( FILTER( 'fact' , 'fact'[caldate] = _caldate) , [amount])
var _amount = 'fact'[amount]
return
DIVIDE(_amount,_sum) * 'fact'[working days]

(2)Then we can put this in the visual and we will meet your need :

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi Aniya,

thanks a lot for your help!
sorry forgot to emphasize it needs to be able to filter using 2 dimensional tables (shown in screenshot of data model), and based on that selection I have to calculate share% and adjusted working days... 

Your example works for when nothing is filtered, but when I use filters from 2 dimensional tables (hierarhies), it doesn't look correct - I assume that's because it should be calculated in runtime, as a calculated measure, instead of column?

I tried to describe desired output sample in excel:
https://1drv.ms/x/s!AtNDPFIXfkrDaWBYevbK-BojBbU?e=CbwSqw 

so if I select 2 companycodes comp7 and comp69:

I should get this as a result:

And using your calculated column it produces this:

I'm pretty sure I have to calculate it as a measure, but ALL and ALLEXCEPT is not behaving as I want it to 🙂
Thank you for your help, it is very much appreciated!

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 
Yes you are right, if you want to filter in Power BI Desktop, you need to create a measure not a calculated column . I retest the data with your need and realize it . Here are the steps you can refer to :

(1)We need to update the measure we create :

Measure = 
var _compay =VALUES(dim_comp[CompanyCodeKey])
var _dimension = VALUES(dim_dim1[dimension1])
var _t =SELECTCOLUMNS( ADDCOLUMNS( SUMMARIZE( 'fact' , 'fact'[compcode] , 'fact'[dimension1] , "amount" , SUM('fact'[amount]) , "sum" , CALCULATE(SUM('fact'[amount]) ,TREATAS(_compay,'fact'[compcode]),TREATAS(_dimension,'fact'[dimension1]), ALL('fact'),VALUES('fact'[caldate])  ) ) , "share%" , DIVIDE( [amount],[sum])) , "compcode2" , [compcode] , "dimension2" , [dimension1] , "share%" , [share%])
var _t2 = SELECTCOLUMNS('fact' , "compcode",[compcode] ,"dimension1",[dimension1],"Working days",[working days])
var _t3 =ADDCOLUMNS( FILTER( CROSSJOIN(_t,_t2) ,[compcode2] = [compcode] && [dimension1]= [dimension2]) , "Working days adjust" , [share%] * [Working days])
return
SUMX(_t3,[Working days adjust])

(2)Then we can meet your need , the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Dear Aniya,

thanks, this looks great! It took me a while to digest and understand what and why, and I still have some questions:

1. why TREATAS() and CROSSJOIN()? Aren't data model relations enough to determine how the data should be connected?

2. I know it's a bit dumb question, but where can I learn more about above mentioned functions, with good practical examples? When reading the definition and basic examples it's hard sometimes understand when to use it...

And final question if I may - from the final result, how would we take the last adjusted working day and show it on each row, per caldate?

I cannot just take MAXX() cause it will take the highest working day...

Goal here is to know how much reamining working days are left, based on newly adjusted calculated measure (measure_max is just hardcoded number, to demonstrate the final result):

Thank's a ton for your help, as you can see I'm still learning how complex and powerful dax can be, and I think I'm at the point of understanding and using it properly in more demanding calculations - with your help I'm on that path, so thank you for that 🙂

[v-yueyunzh-msft]

Hi , @Anonymous 

For your questioon ,here is my understand:

Q1:why TREATAS() and CROSSJOIN()? Aren't data model relations enough to determine how the data should be connected?

Of course, the relationships between data models can be filtered, but the first step in your requirements calculation is to group according to the [dimension1] and [compcode] fields, and it is impossible to use model relationships directly without creating virtual table calculations. The relationships between models simply modify the current filtering context in which the measure is calculated, not grouping. So I create the virtual table first and reconstruct the relationship using the TREATAS() function to calculate the [share%] you need. Second, your second step is to assign [share%] to the initial table,Since the virtual table itself has no corresponding relationship, I use CROSSJOIN() for association.

Q2:You need to calculate the "the last adjusted working day", do you mean the max of the [adjusted working day] per dat?

You can update the "return" of the Measure , like this :

Measure2 = 
var _compay =VALUES(dim_comp[CompanyCodeKey])
var _dimension = VALUES(dim_dim1[dimension1])
var _t =SELECTCOLUMNS( ADDCOLUMNS( SUMMARIZE( 'fact' , 'fact'[compcode] , 'fact'[dimension1] , "amount" , SUM('fact'[amount]) , "sum" , CALCULATE(SUM('fact'[amount]) ,TREATAS(_compay,'fact'[compcode]),TREATAS(_dimension,'fact'[dimension1]), ALL('fact'),VALUES('fact'[caldate])  ) ) , "share%" , DIVIDE( [amount],[sum])) , "compcode2" , [compcode] , "dimension2" , [dimension1] , "share%" , [share%])
var _t2 = SELECTCOLUMNS('fact' , "compcode",[compcode] ,"dimension1",[dimension1],"Working days",[working days])
var _t3 =ADDCOLUMNS( FILTER( CROSSJOIN(_t,_t2) ,[compcode2] = [compcode] && [dimension1]= [dimension2]) , "Working days adjust" , [share%] * [Working days])
return
MAXX(_t3,[Working days adjust])

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

HI Aniya,

another reply with great tips - thank you. Your answer on Q1 has given me a bit different perspective on how to do calculations in the future.

Regarding Q2, I would need max adjusted working day per compcode and dimension 1, without caldate - the goal is to show how many adjusted working days we have remaining, so idea was to have a formula:

[remaining working days] = [max adjusted working days for selection (it's always going to be adjusted working days on last day in month)] - [adjusted working days, per date]

in example I showed as final result:

then I can simply calculate in another measure, for example on 11 Oct:

21.98 - 7.98 = 14 adjusted working days remaining.

Let me know if this doesn't make sense and I'll try to preformulate and explain differently.

Thank you!

[v-yueyunzh-msft]

Hi , @Anonymous 

For your second need , you can use this dax :

Measure2 = 
MAXX(ALL('fact'[caldate]),[Measure]) - [Measure]

The result is as follows:

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi Aniya,

sorry for late response, we've had some holiday's past few days.
DAX in "Measure 2" I actually had the same formula in mind just that I mixed "caldate" field with another (where I have only day our of calendar field).

Thank you very much for all the help, you've helped me understand real-life example much easier and quicker!