Legend from dynamic measure

[haurg]

Hi,

I want to split this stacked line chart by the measure `cat`.

Problems:
(1) But I cannot select a measure as a legend. I tried to work with field parameters, but failed.
(2) I would like to aggregate by day, week, month as the second step (This functionality exists already)

I tried a couple of YouTube videos for it already, but all did not give me the result

Code to generate the measure cat:

cat = 
SWITCH(
    TRUE(),
    SUM(z_cat_test[sales]) > 0 && [acos_test] >= 1, "ACoS >= 1",
    SUM(z_cat_test[sales]) > 0 && [acos_test] < 1 && [acos_ppc] >= 0.3, "0.3 <= ACoS < 1",
    SUM(z_cat_test[sales]) > 0 && [acos_test] < 0.3, "ACoS < 0.3",
    SUM(z_cat_test[sales]) = 0, "No revenue",
    "Other"
)

Here is a link to the data set: 
https://docs.google.com/spreadsheets/d/1DPknFqBUSfle4ioUAeEclBipfSNqKDUXhnCyTEr-r0E/edit#gid=0

Thanks

[NaveenGandhi]

Hello @haurg 

Can you tell what is acos_test? 

[haurg]

Sure,

this is a measure

acos_test = DIVIDE(SUM(z_cat_test[ad_spend]), SUM(z_cat_test[sales]), BLANK())

[NaveenGandhi]

You can create cat as a column, so that you can use it as a legend.

I have removed the second category as i did not know acos_ppc calculation. Also create acos_test using power query, Creating it as a column using dax will create circular dependency.

For the 2nd problem, since this a column now, you can aggregate it day, week, month using date column.

Hope this helps! let me know if you have any issues.

If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

[haurg]

Creating a column does not work as I might have multiple entries for one campaign per day.

I cannot aggregate the acos_test as this is a percentage and needs to be calculated based on the selection/ slicing

[NaveenGandhi]

WIll summarizing the table is fine with you, as it would take care of multiple entries for one campaign per day. Can you try the below Dax as a new table and Check?

Lengend = SUMMARIZE(SUMMARIZE(z_cat_test,z_cat_test[campaign_name],z_cat_test[date],"Sales",SUM(z_cat_test[sales]),"ad spend",sum(z_cat_test[ad_spend]),"acos_test",DIVIDE(SUM(z_cat_test[sales]),sum(z_cat_test[ad_spend]),BLANK())),z_cat_test[campaign_name],z_cat_test[date],[Sales],[ad spend],[acos_test],"cat",SWITCH(TRUE(),SUM(z_cat_test[sales]) > 0 && [acos_test] >= 1, "ACoS >= 1",SUM(z_cat_test[sales]) > 0 && [acos_test] < 0.3, "ACoS < 0.3",SUM(z_cat_test[sales]) = 0, "No revenue","Other"))

[haurg]

When I summarize it by day, what do I do in order to summarize it by week, month, year?

Any chance we can work with field parameters etc.?

[NaveenGandhi]

You still have the date in summarized table, you can use that to create date hierarchy and visualize it by week, month and year

I don't think field parameters will be useful for this scenario.