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.
Dears,
Having a data format something like this:
MONTH | SELLER | CUSTOMERS | SALES | CONVERSION RATE % |
2021.01.01 | LUCY | 10 | 1 | 10% |
2021.01.01 | PAUL | 5 | 0 | 0% |
2021.02.01 | LUCY | 12 | 1 | 8% |
2021.02.01 | PAUL | 6 | 1 | 17% |
2021.03.01 | LUCY | 15 | 2 | 13% |
2021.03.01 | PAUL | 6 | 1 | 17% |
2021.04.01 | LUCY | 17 | 2 | 12% |
2021.04.01 | PAUL | 7 | 1 | 14% |
And it is possible to convert to MATRIX, but how could I make any addition or substraction between columns in a matrix to get data like the BOLD one?
Lucy | Paul | ||||||
CUSTOMERS | SALES | CONVERSION RATE % | CUSTOMERS | SALES | CONVERSION RATE % | CONVERSION RATE DIFF | |
2021.01.01 | 10 | 1 | 10% | 5 | 0 | 0% | -10% |
2021.02.01 | 12 | 1 | 8% | 6 | 1 | 17% | 8% |
2021.03.01 | 15 | 2 | 13% | 6 | 1 | 17% | 3% |
2021.04.01 | 17 | 2 | 12% | 7 | 1 | 14% | 3% |
2021.05.01 | 19 | 3 | 16% | 9 | 1 | 11% | -5% |
2021.06.01 | 22 | 3 | 14% | 15 | 2 | 13% | 0% |
2021.07.01 | 24 | 4 | 17% | 16 | 2 | 13% | -4% |
2021.08.01 | 27 | 4 | 15% | 17 | 3 | 18% | 3% |
2021.09.01 | 29 | 5 | 17% | 18 | 3 | 17% | -1% |
2021.10.01 | 32 | 5 | 16% | 19 | 3 | 16% | 0% |
2021.11.01 | 35 | 6 | 17% | 19 | 4 | 21% | 4% |
2021.12.01 | 37 | 6 | 16% | 20 | 4 | 20% | 4% |
Thank you in advance!
Solved! Go to Solution.
@vjuhasz see attached for an implementation. Here is the measure definition:
CR =
if(HASONEVALUE('Table'[SELLER]),AVERAGE('Table'[CONVERSION RATE %]),
var minName=MINX('Table','Table'[SELLER])
var maxName=MAXX('Table','Table'[SELLER])
var a=SUMMARIZE('Table','Table'[MONTH]
,"mn",CALCULATE(min('Table'[CONVERSION RATE %]),'Table'[SELLER]=minName)
,"mx",CALCULATE(max('Table'[CONVERSION RATE %]),'Table'[SELLER]=maxName))
return AVERAGEX(a,[mn]-[mx]))
@vjuhasz see attached for an implementation. Here is the measure definition:
CR =
if(HASONEVALUE('Table'[SELLER]),AVERAGE('Table'[CONVERSION RATE %]),
var minName=MINX('Table','Table'[SELLER])
var maxName=MAXX('Table','Table'[SELLER])
var a=SUMMARIZE('Table','Table'[MONTH]
,"mn",CALCULATE(min('Table'[CONVERSION RATE %]),'Table'[SELLER]=minName)
,"mx",CALCULATE(max('Table'[CONVERSION RATE %]),'Table'[SELLER]=maxName))
return AVERAGEX(a,[mn]-[mx]))
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 |
---|---|
5 | |
1 | |
1 | |
1 | |
1 |