cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hyp09
New Member

Row based calculation

Request your help on the following:

I have measure X and Y in ROWS for

Time Periods Current week (CW), Month to Date (MTD), Year to Date (YTD) 

Department D1, D2, D3

 

I want to display X and Y values in front of measure Z in two different columns for the same time period and same department.

I need to do this to achieve drill functionality (roll up department to groups, join is there in modelling)...I cannot use pivot option

 

MeasureTime_PeriodDepartmentCYNew_Measure_COLUMN_XNew_Measure_COLUMN_Y
XCWD11  
XMTDD22  
XYTDD33  
YCWD14  
YMTDD25  
YYTDD36  
ZCWD1014
ZMTDD2025
ZYTDD3036
1 ACCEPTED SOLUTION

Thanks. I created a duplicate table, created a key for left outer join and then I wrote conditional DAX statements ...long route but it worked 

View solution in original post

9 REPLIES 9
adnanzakir
Helper III
Helper III

Hi @Hyp09 .

Agree with @AllisonKennedy, using the matrix visual will achieve similar to what you are trying to achieve. However, can you provide with some more information on what exactly were you trying to achieve. Also a sample of raw data would be helpful. 

My measure Z is based on a ratio of X and Y. I need to create a mesaure to handle the calculation of ratio for drill up/down option. I am familiar with writing measure if I have X and Y in columns but here I have in rows. So I am trying to create rows into columns by creating two seperate columns and refrencing X and Y values fro same department and same time period. I cannot use pivot option as there is lot of other measures (without ratio) and those measures work fine in rollup. I have a slicer to select measures...so I want to include in the same table rather than creating a different visuals based on different table

Hi @Hyp09 

Have you tried using the ALLEXCEPT function for the measure. That will help with the drillthrough issue you're facing. 

Alternatively, there was similiar problem resolved on this link: 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Removing-drillthrough-filters-in-a-measure/m-... 

@Hyp09  Maybe try:

 

Measure = DIVIDE ( CALCULATE( [Measure], FILTER(table, Table [measure = X] ), CaLCULATE{ [Measure], FILTER(table, Table [measure = Y] ) ) )

 

Or something similar might be what you're looking for?

 

 



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Thanks. I created a duplicate table, created a key for left outer join and then I wrote conditional DAX statements ...long route but it worked 

View solution in original post

@Hyp09  Glad this is solved. Please mark the solution that solves it or elaborate on what you did and accept your own post as a solution so others can benefit too. 🙂 Thanks!



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

AllisonKennedy
Super User III
Super User III

@Hyp09  I'm not sure what you're trying to do - what does the raw data look like? What is your value column? 

 

You can use the 'matrix' visual to achieve similar to what you want I think. 



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

I have to get these values for measure Z first before going to visualization. Once I get New_Measure_COLUMN_X and New_Measure_COLUMN_Y calcualted based on X and Y, I have to add a quick measure to calcualte New_Measure_COLUMN_X/New_Measure_COLUMN_Y to handle the rollup. My value column is CY...I am putting my example here again

MeasureTime_PeriodDepartmentCYNew_Measure_COLUMN_XNew_Measure_COLUMN_Y
XCWD11  
XMTDD22  
XYTDD33  
YCWD14  
YMTDD25  
YYTDD36  
ZCWD1014
ZMTDD2025
ZYTDD3036

 

@Hyp09  

 

Please can you provide more relatable example of what the RAW data looks like (before you do any calculations, measures or transformations). Measures do not exist within the data table, so please leave those out and only include the table columns. 

 

If a column contains numeric values, provide samples of what type of numbers, range, are there duplicate values, etc. 

 

Finally, provide the FINAL output desired - you have an idea in your head for how you want to solve, but we haven't spent all the time you have with the data, so we can't understand your solution without more context and detail. 

 

Also, someone might have a more efficient way than your idea that you're seeking help on, that might be easier to implement, so if you stick to providing raw sample input and desired final output, you'll get more help and better solutions. 

 

Hope that makes sense?



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors