Pivot Table Target Measure

[Anonymous]

Hi guys... I need your help with a measure in Pivot Table with Excel.

My goal is to have a measure (DisplayTotalWeeks) based on qty of "SemanaMes". In the case, it must return 9 like the example below.

Something like that:

Criada em (Month) SemanaMes Qty Atividade DisplayTotalWeeks

abr	1	446	9
abr	2	11	9
abr	3	1	9
abr	4	3	9
abr	5	1	9
mai	1	524	9
mai	2	701	9
mai	3	1	9
mai	4	2	9
Grand Total		1690	9

I have stressed ChatGPT, but it doesn't give me what I'm looking for.

As an example, I have try this from GPT:
TotalUniqueWeeks := COUNTROWS(SUMMARIZE('YourTable', 'YourTable'[SemanaMes]))
DisplayTotalWeeks := CALCULATE([TotalUniqueWeeks], ALL('YourTable'))
It doesn't return 9, that what I was expecting.

So, you are my last hope.

[DimaMD]

@Anonymous  Hi, you need to add another column to the TotalUniqueWeeks measure, Is this your desired outcome?

[Anonymous]

Hi @DimaMD ,

I have had try that, but I got this one:

Criada em (Month) | SemanaMes | Qty Atividade | measure 2

abr	1	446	5
abr	2	11	5
abr	3	1	5
abr	4	3	5
abr	5	1	5
mai	1	524	5
mai	2	701	5
mai	3	1	5
mai	4	2	5

Grand Total 16905

Maybe the problem is here on calculated column:

=COUNTROWS( SUMMARIZE(export_sensedata_atividades_2024_05_13;export_sensedata_atividades_2024_05_13[SemanaMes]))

Before I forget: Slava Ukraini!!!

[DimaMD]

@Anonymous Hi, try it DAX, add in sumarize colum "Criada em (Month)"

=COUNTROWS( 
     SUMMARIZE( 
          export_sensedata_atividades_2024_05_13;  
  export_sensedata_atividades_2024_05_13[SemanaMes];
export_sensedata_atividades_2024_05_13[Criada em (Month)]
 )
)

[Anonymous]

Hi Dima,

I have try this before, and I believe that we are close because the result.

Criada em (Month) | SemanaMes | Qty Atividade | measure 2

abr	1	446	10
abr	2	11	10
abr	3	1	10
abr	4	3	10
abr	5	1	10
mai	1	524	10
mai	2	701	10
mai	3	1	10
mai	4	2	10

Grand Total 16905

Maybe on the measure now, because it must consider what is showed.