Forum Discussion

Rem's avatar
Rem
Frequent Visitor
8 years ago

Use two different filters in a single mesure

Hello everyone,   I have a question regarding the creation of a new measure (which can be named A) by using an other measure (B) that has been already  created. The measure B represents the whole m...
Need Help
Show and Tell
Rem's avatar
Rem
Frequent Visitor
8 years ago

Hi MFelix,

 

Thank you for your answers,

 

Let's see how it works with a concret example :

 

Here is the Measure B which is created thanks to a function. The result is 100 000 products sold in a year in a whole country.

As you can see, I also have two filters which determine a zone and a seller. Several sellers belong to a single zone.

 

 

 

If I'm selecting a zone, I have the number of products which have been sold in it.

Here we can see 5000 products for the 5.

 

 

 

If I'm selecting a seller (represented by a number ex : 1401 = Georges) , I have the number of products which have been sold by him.

Here we can see 600 products for the seller 81.

 

Now I would like to divide in a new measure (A) the result of the third screen by the result of the second. => the number of product sold by a seller compared to his zone.

But the measure A must works with the other zones and sellers.

It is more clear?

 

Thank you for your time,

Regards,

Rem

 

MFelix's avatar
MFelix
Icon for Super User rankSuper User
8 years ago

Hi Rem,

 

You should use something similar to this

 

 

A =
VAR Selected_Seller =
    MAX ( Table[Seller] )
VAR Selected_Zone =
    MAX ( Table[Zone] )
RETURN
    DIVIDE (
        CALCULATE ( SUM ( Table[Column] );Table[Zone] = Selected_Zone);
        CALCULATE ( SUM ( Table[Column] ); Table[Zone] =  Table[Seller] = Selected_Seller  && Selected_Zone )
    )

 

This formula will give in the first part the selected zone quantitys and in the second the seller and zone you have selected.

 

Replace Table[Column] by the measure you have created.

 

Regards.

MFelix