Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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:
@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?
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as 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 C
I work as a Microsoft trainer and consultant, 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
@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!
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as 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 C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as 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 C
I work as a Microsoft trainer and consultant, 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
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 |
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?
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as 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 C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |