Incorrect Total using ISINSCOPE

DAX DAX Calculations
1

Hi all,

I have a formula to show the correct value for Average Total or so I thought, but for some reason it is not calculating correctly. See image #1.

Can someone help? Am I not using InScope correctly?

The formula is

Avg $ RIGHT Total Amount =

IF(
ISINSCOPE( Campaigns[BC #] ),
[Avg $ Global OR Potential],
SUMX(
VALUES( Campaigns[BC #] ),
[Avg $ Global OR Potential]
)
)

Image +1
WIP -.pbix (18.0 MB)

image
image805×611 44.3 KB

2

Hello @ysherriff,

Thank You for posting your query onto the Forum.

In this case, “ISINSCOPE()” function doesn’t have anything to do with fixing the grand totals. This function checks the validity of the condition at each line item level whether it satifies or not i.e., whether “Campaigns[BC #]” exists at line item level or not and provides the results accordingly.

Since you’re putting two fields inside the table visual i.e., “Campaign ID” and “BC #” but inside the measure only “BC #” is considered in the form of “VALUES( Campaigns[BC #] )”. This would’ve been true, only if you’re putting that single field inside the table visual and then evaluating the totals. But in this case, since you’re considering more than one field then context also needs to be adjusted in accordance with that. Below is the revised measure alongwith the screenshot of the final results provided for the reference -

Avg $ RIGHT Total Amount - Harsh =
IF( ISINSCOPE( Campaigns[Campaign ID] ) ,
    [Avg $ Global OR Potential] ,
    SUMX(
        ADDCOLUMNS(
            SUMMARIZE(
                Campaigns ,
                Campaigns[Campaign ID] ,
                Campaigns[BC #] ) ,
            "@Totals" ,
            [Avg $ Global OR Potential] ) ,
        [@Totals] ) )

Final Results
Final Results1232×770 46.6 KB

I’m also attaching the working of PBIX file alongwith the link of a post on topic - “Fix Incorrect Totals” for reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

WIP - Harsh.pbix (18.0 MB)

1 Like
3

Thanks Harsh. I understand the context now. Great explanation.

I come from an excel background, is there a tool where you can analyze a formula step by step. In excel, you can do this Evaluate Formula tool. Is a there a tool where I can do a step through an analysis to get better understanding of how it works.

But you provided great explanation.

Thanks again and to EDNA team.

4

There is a hidden gem in the August 2022 release of Power BI Desktop: a secret new DAX function EvaluateAndLog that is not listed in the feature summary of the official blogpost. Yet this function will become a great learning and debugging tool for anyone who writes DAX code in the years to come.

Read the 3 blogposts written by the tool author to follow instructions. Hope this helps!

1 Like
5

Perfect Pranamg. Excellent and thank you very much for responding. I will be here with EDNA to help along the way.

Thanks a million. Will save to read later…:slight_smile: :smile: