Forum Discussion
Table Visual and Measure Breaks when Adding Another Column
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.
5 Replies
what if you add the date column to your ALLEXCEPT modifier?
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.
Hi Anonymous ,
Please add Month column also in that AllExcept() function.
