Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello @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
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello @MFelix
Here's the link, https://onedrive.live.com/?id=FD0C8315857DF63A%21266&cid=FD0C8315857DF63A
Thank you for the help
Mariem
Hi @Anonymous ,
It's asking a password to acces the folder can you make a shared link please?
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello @MFelix
Sorry, this is the correct link : https://liveeduisegiunl-my.sharepoint.com/:f:/g/personal/m20180240_novaims_unl_pt/EtB2bxuOMIBKkTQ40A...
Regards,
Mariem
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |