DAX _ how to return correct value of a division while having a multiple selection on a slicer

[Anonymous]

Hello everyone,

I’m new to DAX  and I work connected to sql server DB in direct query. My DB contains several fact tables aggregated on different levels ( market or product and different locations[country, region, sector and UGA] ). For instance, fact_sog_mkt_sct : fact on market and sector level ,  fact_sog_sct : fact on product and sector level .

I have the following two tables where quoata product and quota markets are measures :

Quoata product on sector level is :  

QUOTA_PRD_SCT = SUM(FACT_SOG_SCT[GSO_STD_SCT])/sum(FACT_SOG_REG[GSO_STD_REG])  (REG= region)

I want to add an option where there is a possibility of multiple selection of different regions , but when I do that, my measurement return wrong values because instead of divinding the SUM(FACT_SOG_SCT[GSO_STD_SCT]) by the corresponding value of the region it belongs to (for instance B0B01 belonfs to region B) , it instead devides by all the regions. (see pic below)

I know that by adding the column region to my 2nd table, I will have the correct values but I don't want to do that so 

I was wondering if there is a way to change my dax formula in order to avoid this problem or change the table in order to hide a column if possible

Thank you for the help,

[MFelix]

Hi @Anonymous ,

Assuming based on your explanation that theres is a relationship between sector and region so try the following code:

QUOTA_PRD_SCT =
SUM ( FACT_SOG_SCT[GSO_STD_SCT] )
    / CALCULATE (
        SUM ( FACT_SOG_REG[GSO_STD_REG] );
        FILTER ( ALL ( Table[region] ); Table[region] = MAX ( Table[Region] ) )
    )

Should be something similar to this, you need to replace the Table region by the correct column on your model.

Regards,

MFelix

[Anonymous]

Hello @MFelix 

Thank you for your reply. 

I do have a relationship between region and sector using region_id ( i work with a snow flake schema)

I implemented the forumla, it works partially. For instance, if I have region A and B selected, i will have the values of quaota product of all sectors (A0A01 to A0A10)of region A correct but values of sectors for region B are wrong. I also checked with choosing 3 regions, the current formula only gives corrects values of all sector from only one region.

Could you please explain to me the logic behind the formula so I have a better idea how to address similar probelm

Thank you

[MFelix]

Hi @Anonymous ,

The logic of the measure is to force the divisor to get only the value of the Region of the selected line, so each line will return the total for the region it belongs to.

Try the following code:

QUOTA_PRD_SCT =
SUM ( FACT_SOG_SCT[GSO_STD_SCT] )
    / CALCULATE (
        SUM ( FACT_SOG_REG[GSO_STD_REG] );
        FILTER ( ALL ( Table[region] ); Table[region] = SELECTEDVALUE ( Table[Region] ) )
    )

If it still doens't work can you share a sample file or a mockup of your data?

Regards,

MFelix

[Anonymous]

Hello @MFelix 

Here's the link, https://onedrive.live.com/?id=FD0C8315857DF63A%21266&cid=FD0C8315857DF63A 

Thank you for the help

Mariem

[MFelix]

Hi @Anonymous ,

It's asking a password to acces the folder can you make a shared link please?

Regards,

MFelix

[Anonymous]

Hello @MFelix 

Sorry, this is the correct link : https://liveeduisegiunl-my.sharepoint.com/:f:/g/personal/m20180240_novaims_unl_pt/EtB2bxuOMIBKkTQ40A...

Regards,

Mariem

[MFelix · Accepted solution]

Hi @Anonymous ,

I have checked your model and you issues is that you have a cross filtering through tables so you need to redo your measure to:

QUOTA_PRD_SCT_recalc =
SUM ( FACT_prdct_sector[GSO_STD_SCT] )
    / CALCULATE (
        SUM ( FACT_prdt_region[GSO_STD_REG] );
        FILTER ( FACT_prdct_sector; SUM ( FACT_prdct_sector[GSO_STD_SCT] ) <> BLANK () )
    )

I have added this measure to your file and to your table please check if everything is calculating as you need.

Based on my tests everything is working properly.

Regards,

MFelix

Regards,

MFelix

[Anonymous]

@MFelix  Thank you so much , it works !

Can you please explain to me by cross filtering through tables, because I checked cross filter directions and all of them are set to "single".

I also don't understant that by filtering the table Fact_prdct_sector , it somehow makes sure that all values from sector belonging to the same region will only devided by that reagion. 

Thank you,

Mariem,