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.
Hi, I was wondering if this is possible,
I have a matrix with measures on rows and year on columns, so the columns are 2016, 2017 for example. Each row ahas a measure showing a number for 2016 and 2017 for each measure. Is it possible to add a third calculated column to show for example, the difference in the measure between 2016 and 2017? Here is an example of what I mean:
Thanks in advance!
Solved! Go to Solution.
Hi @djk1000,
In the matrix, when we add a measure to the matrix Values section, the measure values will display in each column group. It means the within 2016 group there will have a column, as well as the 2017 group. What we can do is making the columns under 2016 and 2017 group display blank, rather than remove them.
In your scenario, you can create a measure like below:
DiffPercentage = var Y2016= CALCULATE(SUM('Table1'[Value]),FILTER('Table1','Table1'[Year]=2016))
var Y2017= CALCULATE(SUM('Table1'[Value]),FILTER('Table1','Table1'[Year]=2017))
return
IF(SUM(Table1[Value])=SUMX(ALL(Table1),[Value])||SUM(Table1[Value])=SUMX(FILTER(ALL(Table1),[Category ]=MAX(Table1[Category ])),[Value]),DIVIDE(Y2017-Y2016,Y2016),BLANK())
Best Regards,
Qiuyun Yu
I want to filrer on count of mergerd column where it is greater than 1 - this is count of various underlying items. If i am applying filter on field it is taking adhoc- here i want to filter on subtotal
Hi,
I was wondering if this solution did really work for you? I have a similar matrich with approximately 20 measures in the rows (values displayed on rows), but not sure how to get now a column with the differences. If I would add the 'difference' cloumn also to the 'values' section it would appear on the row as well right?
Regards,
Siem
Hi @djk1000,
In the matrix, when we add a measure to the matrix Values section, the measure values will display in each column group. It means the within 2016 group there will have a column, as well as the 2017 group. What we can do is making the columns under 2016 and 2017 group display blank, rather than remove them.
In your scenario, you can create a measure like below:
DiffPercentage = var Y2016= CALCULATE(SUM('Table1'[Value]),FILTER('Table1','Table1'[Year]=2016))
var Y2017= CALCULATE(SUM('Table1'[Value]),FILTER('Table1','Table1'[Year]=2017))
return
IF(SUM(Table1[Value])=SUMX(ALL(Table1),[Value])||SUM(Table1[Value])=SUMX(FILTER(ALL(Table1),[Category ]=MAX(Table1[Category ])),[Value]),DIVIDE(Y2017-Y2016,Y2016),BLANK())
Best Regards,
Qiuyun Yu
ho dow you hide the blank columns?
I am trying to following what is in the posting and have got a far as below, it is coming up with a syntax for ',' is incorrect, I cannot see where the problem lies.
Try a measure like this, or adjust it:
Δ 2017/2016 = CALCULATE ( DIVIDE ( SUM('table'[2017]); SUM('table'[2016]) ) - 1 )
... and then convert to %
I don't think that will work, this is in a matrix and the 2016 & 2017 headings are from the year column in the date dimension. so the calculations are on row and the date dimension is on columns, I basically want to add a third 'Year' column that shows the difference between the other two.
Thanks,
Hi @djk1000,
if I understood correctly, you can use SUMX to calculate on rows instead of SUM, "SUMX ( table ; table[column] )", on the other hand, if doesn't work, you can create a measure to filter 2017 and another to filter 2016, and then invoke in this measure instead of use Sum.
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 |
---|---|
108 | |
98 | |
79 | |
65 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |