topic Re: Calculated column for the duplicated account to sum the forecast values in DAX Commands and Tips

Calculated column for the duplicated account to sum the forecast values

Anonymous — Mon, 16 Sep 2024 08:14:16 GMT

Hi Experts,

I am working on the below case:

Requirements : We have to sum all rows with the same account names but different Offer Types , if the sum of the forecast value is >500K then it is Key Account "Yes" else Key Account "No"

for example :

Account	Offertype	forecast	key accounts
AAA	Q	2334
BBB	Q	34556
CCC	A	675454
AAA	A	499523
DDD	Q	65454
BBB	A	9453

Row 1 : Account name is AAA, Offer type is Q and Forecast value is 2334
Row 4 : Account name is AAA , Offer type is A and Forecast value is 499523 then the sum of the AAA is >500K then it is "Yes". if row 1 & row 2 has less than 500K forecast value then it is "No"

Same logic for row 2 & 6 , I need to add this calculated column "Key Accounts" into page filter pane and select Yes or No in different bookmarks. So the account name should be unique with summing forecast values when it publishing in the table visual (no duplicate account names displaying)

Please help to achieve this asap

Thanks

Re: Calculated column for the duplicated account to sum the forecast values

bhanu_gautam — Mon, 16 Sep 2024 09:53:11 GMT

Anonymous First create a new calculated column

Total Forecast =
CALCULATE(
SUM('Table'[forecast]),
ALLEXCEPT('Table', 'Table'[Account])
)

Then create one more calculated column to determine if the account is a Key Account based on the total forecast value:

Key Account =
IF('Table'[Total Forecast] > 500000, "Yes", "No")

Remove duplicate account names by creating a summarized table.

SummaryTable =
SUMMARIZE(
'Table',
'Table'[Account],
"Total Forecast", SUM('Table'[forecast]),
"Key Account", IF(SUM('Table'[forecast]) > 500000, "Yes", "No")
)

Create a relationship between the original table and the summarized table if necessary.

Add the Key Account column to the filter pane. You can now use this column to filter your data based on whether the account is a Key Account or not.

Create bookmarks to save different views (Yes or No) and use them as needed.

Re: Calculated column for the duplicated account to sum the forecast values

Anonymous — Mon, 16 Sep 2024 11:13:54 GMT

bhanu_gautam Thanks for your solution 🙂

I have tried to use the above solution , but I am already having a Total FY Forecast measure to use "

Total FY Forecast = SUMX(VALUES('Test Delivery Updates'[FY Forecast]),CALCULATE(DISTINCT('Test Delivery Updates'[FY Forecast])))
Because I have many duplicate rows after made a unpivot column concept , so that i have used DISTINCT in the above measure , I am using this measures in the Table visual .

So I can't use this measure in the summarize DAX .

VAR _summarize =

SUMMARIZE (

'Test Delivery Updates',

'Test Delivery Updates'[Account Name],

'Test Delivery Updates'[Key Account 1]

)

VAR _AcName = 'Test Delivery Updates'[Account Name]

VAR _FYForecast =

SUMX ( FILTER ( _summarize, [Account Name] = _AcName), [Total FY Forecast] ) -- used my existing measure here

RETURN

IF ( _FYForecast > 500000, "Yes", "No")

Above is the query i am trying to use but no luck 😞

Re: Calculated column for the duplicated account to sum the forecast values

Anonymous — Tue, 17 Sep 2024 03:10:50 GMT

Hi Anonymous ,

I create a table as you mentioned.

Then I create a measure and here is the DAX code.

Column = VAR _AccountSum = CALCULATE( SUM('Table'[Forecast]), ALLEXCEPT('Table', 'Table'[Account]) ) RETURN IF(_AccountSum > 50000, "Yes", "No")

So I think you can use a slicer and create a new table.

Best Regards

Yilong Zhou

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