Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Summarize with filter

Hi!

 

Hope somebody can help me with this.

 

I've summarized a table from a master table where I have monthly sales by IdPais, IdZonaGestion, and other dimensions. The current code for the summarized table is:

Tabla Devaluación = 
SUMMARIZE(
	'Ventas Mensuales';
	'Ventas Mensuales'[IdPais];
	'Ventas Mensuales'[IdZonaGestion];
	"ML";
	CALCULATE(
		SUM(
			'Ventas Mensuales'[ML]
		);
		ALLEXCEPT(
			'Ventas Mensuales';
			'Ventas Mensuales'[IdPais]
		);
		USERELATIONSHIP(
			'Ventas Mensuales'[Fecha];
			'Período'[Fecha]
		)
	)
)

This works pretty good for me, since the final objective is to have sales by IdPais with its respective IdZonaGestion. So, in this scenario, I would have the same value for each country no matter what IdZonaGestion it has.

 

The only problem is that using the function ALLEXCEPT also excludes other filters that I would want to keep. I have report filters that I want to keep and I also want it to be dynamic depending on the selection of the period.

 

How should I change this code to perform as described? Should I use a other function and not ALLEXCEPT?

 

Here some pics of model and report:

 

Thanks in advance!

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Anonymous,

     

    According to your description above, I don't think your problem is with using ALLEXCEPT function which excludes other filters. It is that the Calculate table is evaluated during the database processing(like a data refresh) and then stored in the model, and it cannot be affected by user selection or other filters on the report.

     

    In this scenario, I would suggest you to create a measure to calculate "ML", then show the measure on a Table visual with "IdPais", and "IdZonaGestion" column on the report. :smileyhappy:

    ML =
CALCULATE (
    SUM ( 'Ventas Mensuales'[ML] );
    ALLEXCEPT ( 'Ventas Mensuales'; 'Ventas Mensuales'[IdPais] );
    USERELATIONSHIP ( 'Ventas Mensuales'[Fecha]; 'Período'[Fecha] )
)

     

    Regards

4 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi Anonymous,

     

    According to your description above, I don't think your problem is with using ALLEXCEPT function which excludes other filters. It is that the Calculate table is evaluated during the database processing(like a data refresh) and then stored in the model, and it cannot be affected by user selection or other filters on the report.

     

    In this scenario, I would suggest you to create a measure to calculate "ML", then show the measure on a Table visual with "IdPais", and "IdZonaGestion" column on the report. :smileyhappy:

    ML =
CALCULATE (
    SUM ( 'Ventas Mensuales'[ML] );
    ALLEXCEPT ( 'Ventas Mensuales'; 'Ventas Mensuales'[IdPais] );
    USERELATIONSHIP ( 'Ventas Mensuales'[Fecha]; 'Período'[Fecha] )
)

     

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi v-ljerr-msft. Thanks for your contribution!

       

      Your specific example, helped me to work it out.

       

      Thanks!

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi Phil_Seamark, thanks for your reply.

       

      I read the article. Sadly, I have to say that it didn't help me much. I tried what's mentioned in the article but still not getting what I need. I probably need a more detailed help using my specific scenario.

       

      Thanks for your help.