Total value in a Power BI visual not reflecting the filter applied

[Ravitomar]

Hi All,

I have a scenrio where I need to give global access to user on Sales data on all dimensions but when he wants to check the total sames bases on sales person it should filter to specific country.
Table Structure :  Dim Name table has relationshp with Sales table and there are other dim table also like country and Product. Here Person Can checking Total Sales on Country and Product full but if he wants to check on Name it shold be resticted to CHN Country only.

Measure I am using : 

Total_Sales_base = SUM('Sales'[Amount]).

-----------Final Measure,  If We have filtered on Dim Name we are filtering it to country CHN.

[Total Sales] = If(

                           ISFILTERED('Dim Name'),

                          Calculate(Total_Sales_base, 'Sales'[Country]="CHN"),

                           Calculate(Total_Sales_base)

                          )
-----------------------------------------------
Here We are getting expected output, but the grand total is still showing global number, not reflecting total of CHN as below :

Name	Total Sales
AA	400
DD	346
Total	2669

 
Any suggestion, what changes we can made here in the Measure or Model to reflect actual total or any other workaround. 

Thanks,
Ravi 

[v-sgandrathi]

Hi @Ravitomar,

First load the data in Power BI In, then in Model View, create a one-to-many, single-direction relationship from Dim Name[ID] to Sales[NameID]. This links each sales record to the correct name and ensures filters flow from the dimension to the fact table for accurate reporting.
In your Sales table, create the base measure: Total_Sales_base = SUM('Sales'[Amount])

Create this new measure to apply the CHN filter only when Name is used:
Total Sales =

IF (

    HASONEVALUE('DimName'[Name]),

    CALCULATE([Total_Sales_base], 'Sales'[Country] = "CHN"),

    SUMX(

        VALUES('DimName'[Name]),

        CALCULATE([Total_Sales_base], 'Sales'[Country] = "CHN")

    )

)

Next, create a Table Visual and add DimName'[Name] and [Total Sales] measure
Following these steps will give you the expected result.

Additionally, I have included the PBIX file that I created using the provided sample data. Kindly review it and confirm whether it aligns with your expectations.

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

Thank you.

[Ravitomar]

Hi @v-sgandrathi , 
     Your solution working fine for Name Dimension but it is restrcting numbers for other dimension also like Country and Product. As I mentioned it should give global number for other dimension like if we want to display on Country or Product the output should be below : 

Here total while checking on Name is still 2669 while it should be 746. For other Dim it should be 2669.

Thanks,

Ravi

[v-sgandrathi]

Hi @Ravitomar,

The issue you're facing is due to the current DAX measure applying the "CHN" country filter globally, even when you're analyzing by other dimensions like Country or Product. To resolve this, you need to modify your measure so that the "CHN" filter is only applied when the report is grouped by the Name dimension, and not in other contexts. The existing measure uses HASONEVALUE, which doesn't control the visual context precisely enough, especially for grand totals. Instead, replace HASONEVALUE('DimName'[Name]) with ISINSCOPE('DimName'[Name]), which accurately checks whether the visual is currently displaying data at the Name level.

This change ensures that the "CHN" filter is applied only when the Name dimension is being used in the visual, while for other dimensions like Country or Product, the measure will return the global sales total without restriction. This way, your totals and breakdowns will behave correctly in all visual contexts.

Happy to help! If this addressed your concern, marking it as "Accepted Solution" and giving us "kudos" would be valuable for others in the community.

Thank you.

[v-sgandrathi]

Hi @Ravitomar,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Thank you.

[Ravitomar]

Hi @v-sgandrathi ,

Thanks for following of on this, but no luck yet. I have tried revised solution also.

[v-sgandrathi · Accepted solution]

Hi @Ravitomar,

Try using this measure for the desired answer:

[Total Sales] = 
IF(
    ISINSCOPE('Dim Name'[Name]),
    CALCULATE([Total_Sales_base], KEEPFILTERS('Sales'[Country] = "CHN")),
    [Total_Sales_base]
)

I have included the PBIX file that I created using the provided sample data. Kindly review it and confirm whether it aligns with your expectations.

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

Thank you.

[v-sgandrathi]

Hi @Ravitomar,

As we did not get a response, may I know if the above reply could clarify your issue, or could you please help confirm if we may help you with anything else?

And if the provided information meets your requirements, you can Accept the solution and also give Kudos on that reply. It helps other users who are searching for this same information and find the information.

Your understanding and patience will be appreciated.

[Ravitomar]

Hi @v-sgandrathi ,

Thanks for the help, It couldn't resolve the issue fully but helps a lot. It looks like having single easure is not a solution here. I created two measures here, 1 for global access and 1 for Name dimension only.
For Global measure, I put restriction on Name dimension so it shows blank when check on Name level.

Thanks,

[maruthisp]

Hi Ravitomar,

As per my understanding, you are close to calcuate as per row level.
Try below DAX expression to do with SUMX:
Total Sales (Fixed Total) =
IF(
ISFILTERED('Dim Name'),
SUMX(
VALUES('Dim Name'[Name]),
CALCULATE([Total_Sales_base], 'Sales'[Country] = "CHN")
),
[Total_Sales_base]
)

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated — thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X 

[Ravitomar]

Hi @maruthisp , No Luck. This is also giving the same output, total is showing global values.

[bhanu_gautam]

@Ravitomar , Try using

DAX
[Total Sales] =
IF(
ISFILTERED('Dim Name'),
SUMX(
VALUES('Dim Name'),
CALCULATE(Total_Sales_base, 'Sales'[Country] = "CHN")
),
CALCULATE(Total_Sales_base)
)