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 found already half solution of my problem in this post (https://community.powerbi.com/t5/Desktop/DAX-how-to-change-row-context-to-column/m-p/42610#M16308).
But I need to have the results by a specific date.
Here is a example of how the data looks like and how I want to have it.
Device Category | Date | Avg. Order Value | Conversion | Transaction | Sessions | Sales Line |
desktop | 01.01.2016 | 11,56 | 0,26 | 125 | 14589 | MMDE |
desktop | 02.01.2016 | 11,56 | 0,26 | 125 | 14589 | MMDE |
desktop | 03.01.2016 | 11,56 | 0,26 | 125 | 14589 | MMBE |
desktop | 04.01.2016 | 11,56 | 0,26 | 125 | 14589 | MMBE |
desktop | 05.01.2016 | 11,56 | 0,26 | 125 | 14589 | MMSE |
desktop | 06.01.2016 | 11,56 | 0,26 | 125 | 14589 | MMSE |
desktop | 07.01.2016 | 11,56 | 0,26 | 125 | 14589 | MMGR |
desktop | 08.01.2016 | 11,56 | 0,26 | 125 | 14589 | MMGR |
desktop | 09.01.2016 | 11,56 | 0,26 | 125 | 14589 | MMDE |
desktop | 10.01.2016 | 11,56 | 0,26 | 125 | 14589 | MMBE |
desktop | 11.01.2016 | 11,56 | 0,26 | 125 | 14589 | MMSE |
Date | MMDE | MMBE | MMSE | MMGR | ||
01.01.2016 | 0,26 | 0 | 0 | 0 | ||
02.01.2016 | 0,26 | 0 | 0 | 0 | ||
03.01.2016 | 0 | 0,26 | 0 | 0 | ||
04.01.2016 | 0 | 0,26 | 0 | 0 | ||
05.01.2016 | 0 | 0 | 0,26 | 0 | ||
06.01.2016 | 0 | 0 | 0,26 | 0 | ||
07.01.2016 | 0 | 0 | 0 | 0,26 | ||
08.01.2016 | 0 | 0 | 0 | 0,26 |
When I use the DAX expression from the post I get the same amount of each date.
Calculated Column:
MMDE = CALCULATE(AVERAGEX(ALL_DATA_ALL;ALL_DATA_ALL[Ecommerce Conversion Rate]);ALL_DATA_ALL[Sales Line]="MMDE")
Thanks you in advance.
Solved! Go to Solution.
@Anonymous
1) It seems you want to create a New Table right? (using SUMMARIZE)
2) And you are entering your MMDE formula as a column in this new Summary table?
however those 2 tables are probably NOT related and that's why you are getting this answer!
3) You should be incorporating your MMDE formula as part of the SUMMARIZE statement when creating the table
OR
set up a relationship between the 2 tables.
Summary Table = SUMMARIZE ( ALL_DATA_ALL, ALL_DATA_ALL[Date], "MMDE", CALCULATE ( AVERAGE ( ALL_DATA_ALL[Conversion] ), ALL_DATA_ALL[Sales Line] = "MMDE" ), "MMBE", CALCULATE ( AVERAGE ( ALL_DATA_ALL[Conversion] ), ALL_DATA_ALL[Sales Line] = "MMBE" ), "MMSE", CALCULATE ( AVERAGE ( ALL_DATA_ALL[Conversion] ), ALL_DATA_ALL[Sales Line] = "MMSE" ), "MMGR", CALCULATE ( AVERAGE ( ALL_DATA_ALL[Conversion] ), ALL_DATA_ALL[Sales Line] = "MMGR" ) )
Resulting in...
Or if you have a relationship between the tables...
MMDE Column = CALCULATE ( AVERAGEA ( ALL_DATA_ALL[Conversion] ), ALL_DATA_ALL[Sales Line] = "MMDE" )
Hope this helps!
@Anonymous
1) It seems you want to create a New Table right? (using SUMMARIZE)
2) And you are entering your MMDE formula as a column in this new Summary table?
however those 2 tables are probably NOT related and that's why you are getting this answer!
3) You should be incorporating your MMDE formula as part of the SUMMARIZE statement when creating the table
OR
set up a relationship between the 2 tables.
Summary Table = SUMMARIZE ( ALL_DATA_ALL, ALL_DATA_ALL[Date], "MMDE", CALCULATE ( AVERAGE ( ALL_DATA_ALL[Conversion] ), ALL_DATA_ALL[Sales Line] = "MMDE" ), "MMBE", CALCULATE ( AVERAGE ( ALL_DATA_ALL[Conversion] ), ALL_DATA_ALL[Sales Line] = "MMBE" ), "MMSE", CALCULATE ( AVERAGE ( ALL_DATA_ALL[Conversion] ), ALL_DATA_ALL[Sales Line] = "MMSE" ), "MMGR", CALCULATE ( AVERAGE ( ALL_DATA_ALL[Conversion] ), ALL_DATA_ALL[Sales Line] = "MMGR" ) )
Resulting in...
Or if you have a relationship between the tables...
MMDE Column = CALCULATE ( AVERAGEA ( ALL_DATA_ALL[Conversion] ), ALL_DATA_ALL[Sales Line] = "MMDE" )
Hope this helps!
Thanks for your help. It works just perfect.
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |