daniel_19 Tracker

Show total value in stacked column chart from measure calculated in DAX

daniel_19 — Thu, 02 Mar 2023 14:52:25 GMT

I have a measure in dax that calculates the CPV for Google Analytics channels (d_channels).

I have a table of costs and another of sessions.

My measure calculates the CPV for each channel, but when calculating the total it should be the total invested / total sessions, disregarding the context of channels, because some channels do not have investment value only for sessions. However, when using the stacked column chart, it calculates the sum of each channel grouping, when in fact it was supposed to be calculated as total invested/total sessions. That is, instead of the total over the bars being 1.67 (0.49+1.18), it should return the value of 0.71 calculated by the measure. How can I get it to return 0.71 instead of 1.67? Below is the measurement validated by me and apparently correct.

CPV =
var final =
CALCULATE(
SUM(f_COSTS[Value])/[Session]

)

var total =
CALCULATE(
SUM(f_COSTS[Value])/[Session],
ALL(d_Canais)

)

return
SWITCH(

TRUE(),

ISINSCOPE(d_canais[GROUP2]), final,

total

)

Re: Calculate the measure for the previous analysis week

daniel_19 — Thu, 02 Feb 2023 16:00:37 GMT

I solved.

it is necessary to create a classifier, for using OFFSET in the ORDERBY, I concatenated the year of the end of the week from the column date with the week of the year of the end of the week from the column date and then used offset:

CALCULATE([Final Value],
OFFSET(
-1,
ALLSELECTED(
'd_Calendar'[Concat(Year-Week(EndofWeek)],
'd_Calendar'[Week analyze]),
ORDERBY('d_Calendar'[Concat (Year-Week(EndofWeek)]
)
)

d_calendar below:

Calculate the measure for the previous analysis week

daniel_19 — Thu, 02 Feb 2023 12:43:12 GMT

Hello,

How can I calculate the "Value Begin" measure when it always has the same value as the "Final Value" of the previous week of analysis.

In this example the "Final value" is 80,30,30 for the analysis week 26 Dec to 01 Jan and for categories x,y,z. So the "Value Begin" for the following analysis week (02 Jan to 08 Jan) should be 80,30,30 for category x,y,z.

How to do this in Dax?

I tried with OFFSET

CALCULATE([Final Value],
OFFSET(
-1,
ALLSELECTED(d_calendar[Analyze week]),
GROUPBY(d_calendar[Analyze week]))
)

but doesnt work, returned blank