DAX Count() with some records counted twice

[Marpas]

Hi all,
I would need DAX that would count records for me, which would:
1. some categories merge into one total

2. one category would be counted twice

input:

Category	Count
A.1	10
A.2	20
A.1 + B	30
B	40
C	50
D	60
E	70
F	80

output:

Category	Total
A	60
B	70
C	50
D	60
E	70
F	80

if it was only point 1 (merging) I would simply make custom field where A1 & A2 would be changed to A and just run count() on that field, but unfortunately that "A.1 + B" category which needs to be counted towards total of A and total of B makes things harder.

Thanks in advance for any suggestions.

[vivek31]

HI @Marpas 

you can use this Dax measure

Total = 
var a_total = CALCULATE(SUM(Table_[Count]),Table_[Category ] = "A.1") +
              CALCULATE(SUM(Table_[Count]),Table_[Category ] = "A.2") +
              CALCULATE(SUM(Table_[Count]),Table_[Category ] = "A.1 + B")

var b_total = CALCULATE(SUM(Table_[Count]),Table_[Category ] = "B") +
              CALCULATE(SUM(Table_[Count]),Table_[Category ] = "A.1 + B")
              
var c_total = CALCULATE(SUM(Table_[Count]),Table_[Category ] = "C")
var d_total = CALCULATE(SUM(Table_[Count]),Table_[Category ] = "D")
var e_total = CALCULATE(SUM(Table_[Count]),Table_[Category ] = "E")
var f_total = CALCULATE(SUM(Table_[Count]),Table_[Category ] = "F")

RETURN
SWITCH(SELECTEDVALUE(Table_[Category ]),
       "A",a_total,
       "B",b_total,
       "C",c_total,
       "D",d_total,
       "E",e_total,
       "F",f_total)

[johnt75 · Accepted solution]

I would create a helper table with 2 columns, one for the category name you want to use in visuals ( e.g. A ) and one with the category name from the fact table ( e.g. A.1 ). There would be multiple rows for categories A and B.

Create a many-to-many single direction relationship from the helper table to the fact table, so that the helper table filters the fact table. Then you can use the category from the helper table in visuals, and the measure is a simple SUM of the fact table count column.

[Marpas]

Yes, that's what worked the best. I've tried various combinations and at the end helper table was the way to go.

For future reference: There were more niuanses than I've mentioned in intial question.
Count field doesnt exists, is calculated on the fly by counting unique ItemIDs in fact table.
There is another table, let's call it estimation table (coming from different source).
Goal was to calculate fact/estimated ratio per category.

to simply show numbers, creating relationships was sufficient.
But to calculate ratio, I had to create measures in helper table.

CountFact =

    DISTINCTCOUNT(factTable[ItemID])

CountEstimated =

    DISTINCTCOUNT(estimatedTable[ItemID])

Ratio = [CountFact] / [CountEstimated]

[bhanu_gautam]

@Marpas First Create a calculated column to merge categories:

dax
MergedCategory =
SWITCH(
TRUE(),
Table[Category] = "A.1", "A",
Table[Category] = "A.2", "A",
Table[Category] = "A.1 + B", "A",
Table[Category] = "B", "B",
Table[Category] = "A.1 + B", "B",
Table[Category] = "C", "C",
Table[Category] = "D", "D",
Table[Category] = "E", "E",
Table[Category] = "F", "F",
Table[Category] -- Default case
)

Create a calculated table to count the records:

dax
CategoryTotals =
SUMMARIZE(
Table,
Table[MergedCategory],
"Total",
SUMX(
FILTER(
Table,
Table[MergedCategory] = EARLIER(Table[MergedCategory])
),
Table[Count]
)
)

[Marpas]

<<Table[Category] = "A.1 + B", "B",>> will never be executed

[bhanu_gautam]

@Marpas , This is not sum this is column name as mentioned in your data

A.1 + B