How to get rows to totals to add up the subtotals

[masplin]

I have a data set that has a batch number and then doe each batch there are 16 analytes then have calcuations performed on them. To indetify an analyte forma psecific batch I have a [Sample-Analye ID] e.g. 1223344-Morphine is the 133 lines that make up this anaylsis.

Below is an example of the output

My issue is the last  3 columns. As you can see in Peak in Blank flag column 2 of the analtyes have a 1 next to them. The measure was

Measure = 
VAR
BlankCount=COUNTROWS(    FILTER(SUMMARIZE(Samples,Samples[Name],Samples[Blank Flag],Samples[Sample-Analyte ID]),
                                        Samples[Name]="Blank" || Samples[Name]="Double Blank" && 
                                        Samples[Blank Flag]="Pass"
                                    ))

RETURN
        IF(ISBLANK(BlankCount),BLANK(),IF(BlankCount>=2,BLANK(),1))

Each Sample-Analyte ID  has 1 blank and 1 double blank so this tests both of them pass.  If they do a blank and if 1 or more fails its a 1

However the total at the top needs to 2 because 2 of the analytes failed.   The reason for this is 2 failed in THC and 1 failed in Benzo... = 3 so when you do the calcuation with no filter on the analytes it generates a blank

So I thought i could solve this as follows by created a table of each Sample-Analtye, add a column for the blnak count then SUMX that table. However still doesn't work which was a surprise. 

SUMX(ADDCOLUMNS(
                SUMMARIZE(Samples,
                          Samples[Sample-Analyte ID]
                          ),
              "BC",COUNTROWS(    FILTER(SUMMARIZE(Samples,Samples[Name],Samples[Blank Flag],Samples[Sample-Analyte ID]),
                                        Samples[Name]="Blank" || Samples[Name]="Double Blank" && 
                                        Samples[Blank Flag]="Pass"
                                    ))),
                                    IF(ISBLANK([BC]),BLANK(),IF([BC]>=2,BLANK(),1)))

Any suggestions how to get the next level up in thehierachy to be the sum of the level below instead of calculating att hat level?

PBIX is here

Analyte 

Much appreciate as sure it is quite simple

Mike

[Anonymous]

Hi @masplin ,

You should try create a new measure based the [Count Sample] measure.

Measure = var _table=SUMMARIZE('Samples',[Sample ID],[Analyte],[Sample Group],"Count",[Count Sample])
return SUMX(_table,[Count])

The totals of other measures also create new measures as described above.

Best Regards,

Stephen Tao

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

[masplin]

It was the blank flag totla I was tryin g to fix.  I found the solution, but really understand why it works if anyone can explain.

so neither of these versions work

 SUMX(ADDCOLUMNS(
                SUMMARIZE(Samples,
                          Samples[Sample-Analyte ID]
                          ),
              "BC",IF(COUNTROWS(    FILTER(SUMMARIZE(Samples,Samples[Name],Samples[Blank Flag],Samples[Sample-Analyte ID]),
                                        OR(Samples[Name]="Blank", Samples[Name]="Double Blank") && 
                                        Samples[Blank Flag]="Pass"
                                    ))>=2,BLANK(),1)),
                                   [BC])

or using a VAR

Measure = 
VAR
BlankCount=COUNTROWS(    FILTER(SUMMARIZE(Samples,Samples[Name],Samples[Blank Flag],Samples[Sample-Analyte ID]),
                                        OR(Samples[Name]="Blank", Samples[Name]="Double Blank") && 
                                        Samples[Blank Flag]="Pass"
                                    ))

RETURN
 SUMX(ADDCOLUMNS(
                SUMMARIZE(Samples,
                          Samples[Sample-Analyte ID]
                          ),
              "BC",IF(BlankCount>=2,BLANK(),1)),
                                   [BC])

however if I take the VAR bit and make its own measure

Blank Count = COUNTROWS(    FILTER(SUMMARIZE(Samples,Samples[Name],Samples[Blank Flag],Samples[Sample-Analyte ID]),
                                        OR(Samples[Name]="Blank", Samples[Name]="Double Blank") && 
                                        Samples[Blank Flag]="Pass"
                                    ))

Then use this measure inside my main measure it works!!!

Peak in Blank Flag = SUMX(ADDCOLUMNS(
                                    SUMMARIZE(Samples,
                                            Samples[Sample-Analyte ID]
                                            ),
                                "BC",IF([Blank Count]>=2,BLANK(),1)),
                                                    [BC])

Why is this??? Why does a measure outside the calcaution work, but if you put it inside it doesn't. Is this something to do with row context? 

Appreciate any clarity