topic Re: how to calculate collective performance of multiple accounts of sales and target for single customer in DAX Commands and Tips

how to calculate collective performance of multiple accounts of sales and target for single customer

123abc — Thu, 22 Aug 2024 10:07:08 GMT

Hi Experts,

I wants to calculate a column or dax measure for to calculate group of dealers sales and target, in my working one dealer / customer has multiple accounts in which they are invoicing and singned targets, i wants to calculate their collective sales in next column and collective target in next column and then to calculate their achievmeent % agaisnt collective targets and sales and if dealers has only one account then measure shows only his single accounts sales and target in next columns but if have multiple accounts the shows collectively.

But i have thousands and customer and rows database based on this type of challenge.

I have done this in excel file (File is attached) but how to do in Power BI with caculated column or dax measure please guide.

In excel i am doing this like:

M/s Afzal has 11 accounts in which they are invoicing and has singed their targets accross the countary, i assigned Group name "AFZAL" in column 1 to every ID, then in next column mentions "G" against every group name (Column 2) and then mention "H" head of all accounts in next column (Column 3). Now the formula works as it combine all "Afzal" named sales or target against "H" in seperated columns called individual total target or sales after grouping.

Excel sampling picture .... reference file of excel and pbix is also attacehd in one drive:

https://drive.google.com/file/d/1TnJK8-PnseCsouKwotBEGWpVYinDFwmG/view?usp=sharing

Re: how to calculate collective performance of multiple accounts of sales and target for single customer

lbendlin — Fri, 23 Aug 2024 01:54:48 GMT

Not clear to me what you are trying to accomplish. Do you want to show the group target only for the H row?

Re: how to calculate collective performance of multiple accounts of sales and target for single customer

123abc — Fri, 23 Aug 2024 06:43:11 GMT

Thank you very much lbendlin for your such support, yes you reached on my problem, i want to show the group target against "H" in Group Targret Column and if any one has not "H" (mean he is not the part of any group) then his individual target will show in Group Target column. please guide how to do this.

Regards:

Ali Abbas

Re: how to calculate collective performance of multiple accounts of sales and target for single customer

123abc — Fri, 23 Aug 2024 08:37:17 GMT

Group Sales =
VAR CurrentGroup = PlanningTable[Group Name]
VAR IsGroupHead = PlanningTable[Group Head "H"] = "H"
VAR TotalSalesForGroup =
CALCULATE(
SUM(PlanningTable[Sales]),
FILTER(
ALL(PlanningTable),
PlanningTable[Group Name] = CurrentGroup
)
)
VAR GroupHasHead =
CALCULATE(
COUNTROWS(PlanningTable),
FILTER(
ALL(PlanningTable),
PlanningTable[Group Name] = CurrentGroup &&
PlanningTable[Group Head "H"] = "H"
)
) > 0
RETURN
IF(
IsGroupHead,
TotalSalesForGroup,
IF(
GroupHasHead,
BLANK(),
PlanningTable[Sales]
)
)

This measure has solved my issue ... but tahnk you so much for your support.

This formula ensures that only the "H" account in a group displays the total group sales, while other group members show as blank. For accounts not part of any group, their individual sales will be shown.