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

[Lwpro]

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

[Lwpro]

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.

[v-yiruan-msft · 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

[Gayatri_D05]

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. 😊