Measure by month ignoring page filter

[Khor]

Hello,

I have been bashing my head against this the whole day and I can't seem to pull it off.

I have a page with some visualizations and charts and a few page level filters which basically force all the information in the page to be of a certain month, but then I have a line chart in this page that I want to see the values for all different months in the filtered year.

I have managed to get a measure that gives me the total sum of the measure for all months but when I put it in the line chart it doesnt get distributed by months, it just tells me the most recent month and the total.

My current measure looks liek this:

Market. y publicidad Real All = 
    CALCULATE(
        SUM(ZPBI_COPA_MKT_Real[Market. y publicidad Real]);
        ALLEXCEPT(Calendario;Calendario[Year])
    )

The reasoning being that I want to calculate the sum ignoring all the filters except the year one.

How can I make it so that the chart also shows this value separated by months?

Greetings and thanks in advance,

Khor

[v-xicai]

Hi @Khor ,

Try to create measure like DAX below.

Measure1 = CALCULATE(SUM(ZPBI_COPA_MKT_Real[Market. y publicidad Real]),FILTER(ALLSELECTED(ZPBI_COPA_MKT_Real),MONTH(ZPBI_COPA_MKT_Real[StartDate])<=MONTH(MAX(Calendario[Date]))&&MONTH(ZPBI_COPA_MKT_Real[EndDate])>=MONTH(MIN(Calendario[Date]))))

You can choose Clustered column chart visual to display the result, drag the [MONTH] of Calendario[Date] on Axis and drag the measure1 on Value .

Or could you please share your sample data or desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

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

[Khor]

Hello @v-xicai thanks for taking the time of looking at it.

I tried to replicate your DAX code in a measure in my report but I can't seem to get it to work. I do not have StartDate or EndDate in that table.

I can't share the report or the data because it's not my own and I wouldnt want to get in trouble about sharing something I shouldn't. But you are right that I should have mentioned how the source looks like.

In the source table I have Year, Month and € (which is the column called "Market. y publicidad Real). It's a really simple table. I also created a calculated column which has a "fake date" concatenating the year, the month and a 01 so that I can filter this table using the calendar one.

In the report I'm showing the value for last month, the YTD value and then in the bottom I need the line chart that shows the evolution of the values throughout the months of the current year.

I hope it was a clear enough explanation.

[v-xicai]

Hi @Khor ,

You can create measure like DAX below, and let the ZPBI_COPA_MKT_Real[Year] as the source of Slicer visual, put the ZPBI_COPA_MKT_Real[Month] onto the X axis of the Line chart.

Market. y publicidad Real All = CALCULATE(SUM(ZPBI_COPA_MKT_Real[Market. y publicidad Real]),FILTER(ALLSELECTED(ZPBI_COPA_MKT_Real[Year]), ZPBI_COPA_MKT_Real[Year] =MAX(ZPBI_COPA_MKT_Real[Year])&&ZPBI_COPA_MKT_Real[Month] =MAX(ZPBI_COPA_MKT_Real[Month])))

Best Regards,

Amy

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

[Khor]

Hi, thanks for your reply.

The whole point of using page filters was to avoid using slicers in the report area.

In the end we couldn't manage to acomplish it without slicers so we just had to give up and add them to the report and modify the interaction.

It would be nice if we could modify interactions so that specific visuals could ignore page level filters.

[v-xicai]

 Hi @Khor ,

I am not sure what desired result would you want now. Does my reply make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best Regards,

Amy