Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Lwpro
Frequent Visitor

Power Bi when I filter by two regions, the total doesn't match

Hello everyone,

I hope you are doing well.

I need help with a report that I developed, and when I tested it, I encountered the following error.

I created a measure that consists of two others, here they are:

1st Measure
Agreed Targets =
DIVIDE([TOTAL SALES (BASE)], (0.60))

2nd Measure
Target =
IF(SELECTEDVALUE(
'Stores'[Brand]) = "OTHER BRANDS",
0,
CALCULATE( IF([TOTAL SALES (BASE)] <0,
SUMX('Store',[Agreed Targets]), 0)))

3rd Measure
Final Target = SUMX(
VALUES('Products'[Product Description]),
CALCULATE([Target]))

In the report, I use the 3rd Measure because it is the result I expect. However, I found the following error: when I filter through data segmentation and check it by Region, if I filter for only 1 region, the result is correct. However, if I filter for 2 regions, instead of the measure calculating the total for these 2 regions, it gives me a result as if it were an average between them. But I expect it to be the total.


I appreciate your help in advance

1 ACCEPTED SOLUTION

Hi @Lwpro ,

You can update the formula of measure [Final Target] and check if it can return the expected result...

Final Target =
SUMX (
    GROUPBY ( 'Products', 'Products'[Region], [Product Description] ),
     [Target] 
)

In addition, you can refer the following links to try to solve your problem...

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

Best Regards

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

View solution in original post

3 REPLIES 3
Lwpro
Frequent Visitor

Hello Gayatri_D05, how are you?

 

I appreciate your willingness and time to help me, but unfortunately, this solution did not work for me.
I applied it, but even when I filter the data by segmenting it into any two regions, the total with these measures still does not give me the sum of the two regions.

Hi @Lwpro ,

You can update the formula of measure [Final Target] and check if it can return the expected result...

Final Target =
SUMX (
    GROUPBY ( 'Products', 'Products'[Region], [Product Description] ),
     [Target] 
)

In addition, you can refer the following links to try to solve your problem...

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Gayatri_D05
Resolver II
Resolver II

Hi @Lwpro ,
I think its because of the measures it is giving you an value sort of like an average. Instead of using 3 measures. 
Can you try using the following measures :
Measure 1 :
Target =
IF(SELECTEDVALUE(
'Stores'[Brand]) = "OTHER BRANDS",
0,
CALCULATE( IF([TOTAL SALES (BASE)] <0,
SUMX('Store',DIVIDE([TOTAL SALES (BASE)], (0.60))), 0)))

Measure 2 :
Final Target = SUMX(
VALUES('Products'[Product Description]),
CALCULATE([Target]))

I have just added your 1st measure into 2nd instead of passing measure name.
Let me know if this solution works for you.


I hope I was able to resolve your issue. If so please tag this as the answer to help others as well. 😊

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.