Failing to aggregate a measure and total problem

[VDMT]

Hi all, 

I have reviewed Greg's article on Measure Totals, the Final Word - which was useful - but I am as yet stuck on this one!

I have created a sample PBIX (linked below),

The issues I have are:

1. The "Total..." measures for "Brown" and "Red" do not total accurately. I used the technique in the above article to create measure: "Total Brown v3" but the Total does not show up.

2. The Same "Total..." fields are used on the top right Table (Totals against the Category) - should I be using different measures for that?

3. The odd result is that "Green" Totals work across data in different Categories, but Brown and Red do not.

So my expected result, is for the Total Brown, Total Red to SUM according to the corresponding "Flag" value in both the Detailed table (bottom) and the Summary table (top right). At this stage, only the Total Green is working on the both tables. This is a puzzle I have not been able to solve. Any help much appreciated!

Sample report with data - shows the problem faced 

[VDMT]

Thanks @MFelix - so lesson for me is make sure to select a column that is unique (and the correct grain)

[MFelix]

Hi @VDMT ,

You can change your measure to the following code:

Total Brown v3 = 
          IF(HASONEVALUE(CompanyData[Category]), 
             [Brown-Flag], 
           SUMX(ALLSELECTED(CompanyData[Category]),[Brown-Flag])
         )

[VDMT]

@MFelix Hi, unfortunately that does not provide the correct value either, i.e. "1" is not accurate,

I have illustrated the correct values in picture below, the total for "Total Brown v3" should be 7 (which is a SUM of all the "Total Brown v3" across Category AAc, BBBa, CCe:

[MFelix · Accepted solution]

Hi @VDMT ,

The row filter is at the incorrect level in this case you need to do it at company level:

Total Brown v3 = 
IF(HASONEVALUE(CompanyData[Company]), [Brown-Flag],
    SUMX(ALLSELECTED(CompanyData[Company]),[Brown-Flag])
)

Apologies for the miss interpretation