Total by Customer when If statement used

DAX DAX Calculations
1

Hi
Thanks to JBolcher I have my contracts forecasting out at the line level based on the [Forecast?] field being set to “Y”

The issue I am facing is that I need to be able to summarize this by Customer and Contract but when I try to do this the values are incorrect

2022 Forecast should = £2,312 not £2,408. When placing the measure just with the customer or just the contract it appears to be assuming everything is a forecast?

image
image789×601 29.6 KB

Forecasting Test - Upload.pbix (194.2 KB)

I’ve attached the pbix model

2

Hello @AliB,

Thank You for posting your query onto the Forum.

To fix the totals you just need to write a small measure and you’ll see that all the visuals will get fixed and will start reflecting correct grand totals. Below is the measure alongwith the screenshot of the final results provided for the reference -

Total Forecast - Totals = 
SUMX(
    SUMMARIZE(
        ContractDataTest , 
        ContractDataTest[new_contractno] , 
        ContractDataTest[Forecast Out] , 
        ContractDataTest[Forecast?] , 
        ContractDataTest[Forecast Month] , 
        "@Totals" , 
        [Total Forecast] ) , 
    [@Totals]
)

Final Results
Final Results1415×710 47.1 KB

I’m also attaching the working of the PBIX file as well as providing a link below of the post which one of our expert had created which specifically addresses this type of issues.

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

Note: In order to view the entire post, please click onto the link and not onto the “expand/collapse” button.

Thanks and Warm Regards,
Harsh

Forecasting Test - Upload - Harsh.pbix (195.7 KB)

1 Like
3

Hello @AliB, just following up if the response from @Harsh help you solve your inquiry?

We’ve noticed that no response has been received from you since November 1. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

4

Thanks Harsh. Job done :grinning: