Row based calculation

[Hyp09]

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

Measure	Time_Period	Department	CY	New_Measure_COLUMN_X	New_Measure_COLUMN_Y
X	CW	D1	1
X	MTD	D2	2
X	YTD	D3	3
Y	CW	D1	4
Y	MTD	D2	5
Y	YTD	D3	6
Z	CW	D1	0	1	4
Z	MTD	D2	0	2	5
Z	YTD	D3	0	3	6

[Anonymous]

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. 

[Hyp09]

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

[Anonymous]

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-... 

[AllisonKennedy]

@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?

[Hyp09 · 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 

[AllisonKennedy]

@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!

[AllisonKennedy]

@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. 

[Hyp09]

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

Measure	Time_Period	Department	CY	New_Measure_COLUMN_X	New_Measure_COLUMN_Y
X	CW	D1	1
X	MTD	D2	2
X	YTD	D3	3
Y	CW	D1	4
Y	MTD	D2	5
Y	YTD	D3	6
Z	CW	D1	0	1	4
Z	MTD	D2	0	2	5
Z	YTD	D3	0	3	6

[AllisonKennedy]

@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?