[DAX] how to change row context to column in relation to a date

[Anonymous]

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.

[Sean · Accepted 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]

Thanks for your help. It works just perfect.