Table Visual and Measure Breaks when Adding Another Column

[Anonymous]

I don't know how else to title this problem, but this is the best I've got. 

I have a dataset as follows, where I have Locations (TRT_ID) that could have one of two Metrics (Metric_Type), which has monthly data (Use). 

TRT_ID	Metric_Type	Date	Use	Completed
19	Electricity	2022/1/1	8566	Yes
19	Electricity	2022/2/1	8052	Yes
19	Electricity	2022/3/1	9334	Yes
19	Electricity	2022/4/1	8681	Yes
19	Electricity	2022/5/1	7529	Yes
19	Electricity	2022/6/1	9166	Yes
19	Electricity	2022/7/1	9350	Yes
19	Electricity	2022/8/1	11342	Yes
19	Electricity	2022/9/1	0	No
19	Natural Gas	2022/1/1	166.72	Yes
19	Natural Gas	2022/2/1	138.16	Yes
19	Natural Gas	2022/3/1	338.44	Yes
19	Natural Gas	2022/4/1	47.12	Yes
19	Natural Gas	2022/5/1	10	Yes
19	Natural Gas	2022/6/1	0.36	Yes
19	Natural Gas	2022/7/1	0.71	Yes
19	Natural Gas	2022/8/1	0.36	Yes
19	Natural Gas	2022/9/1		No
22	Electricity	2022/1/1	7405.86	Yes
22	Electricity	2022/2/1	7166.25	Yes
22	Electricity	2022/3/1	12397.47	Yes
22	Electricity	2022/4/1	5691.83	Yes
22	Electricity	2022/5/1	6619.3	Yes
22	Electricity	2022/6/1	5988.94	Yes
22	Electricity	2022/7/1	6640.56	Yes
22	Electricity	2022/8/1	6154.99	Yes
22	Electricity	2022/9/1		No

I have a second table that houses information on the location: TRT_ID, Site_Name, etc. , which has an active relationship with Table 1 through TRT_ID

I have created a measure that calculates the Z-Score, grouping by both TRT_ID and Metric Type (i.e. I want z-scores for Jan-Sep usage of electricity for Location 19), and my table currently looks like this. 

Final_Zscore = 

VAR FinalAverage = CALCULATE(

    AVERAGE ( Metrics_Outlier[Use] ),

    ALLEXCEPT ( Metrics_Outlier, Environmental_Data_Collection_Locations[Site_Name], Metrics_Outlier[TRT_ID], Metrics_Outlier[Metric_Type] )

    )

VAR FinalSTDev = CALCULATE(

    STDEVX.P( Metrics_Outlier, Metrics_Outlier[Use] ),

    ALLEXCEPT ( Metrics_Outlier, Environmental_Data_Collection_Locations[Site_Name], Metrics_Outlier[TRT_ID], Metrics_Outlier[Metric_Type] )

)

Return

 (SUM(Metrics_Outlier[Use])- FinalAverage)/FinalSTDev

Everything is FINE, until I try to add "Month" from the date hierarchy. Then suddenly all my values start repeating and everything just breaks. 

I understand that I need to somehow account for the date in the measure, but I just can't figure out what it is that needs to be changed/added to the measure for it to essentially ignore the "date" column completely, so I can just keep it in there for my user's reference. 

[Aburar_123]

Hi @Anonymous ,

Please add Month column also in that AllExcept() function.

[Anonymous]

I've tried this in multiple iterations, and I keep getting repeat rows. 

Use data w/out average column: 

Table when I add the average column

Measure w/ date in AllExcept (I also tried pulling just the month from the Full Date's date hierarchy, AND having a month-only column 😞

[Ashish_Mathur]

Hi,

Try this approach

1. Create a Calendar Table with calculated column formulas for year, Month name and Month number.

2. Sort the Month name column by the Month number.

3. Create a relationship from the Date column of Metrics_Outlier table to Date column of the Calendar Table

4. To your visual, drag Year and Month name from the Calendar Table

5. Your measures can be simplified to

Finalsum = SUM(Metrics_Outlier[Use])

FinalAverage = AVERAGE(Metrics_Outlier[Use])

FinalSTDev = STDEVX.P( Metrics_Outlier, Metrics_Outlier[Use] )

Final_Zscore = divide(([Finalsum]- [FinalAverage]),[FinalSTDev])

Hope this helps.

[lukiz84]

what if you add the date column to your ALLEXCEPT modifier?

[Anonymous]

I get repeat rows no matter what mix of date I put into the allexcept of the measure

Use data w/out average column: 

Table when I add the average column

Measure w/ date in AllExcept (I also tried pulling just the month from the Full Date's date hierarchy, AND having a month-only column ) :