按特定列计算总计，然后将列总计应用于每一行 [英] Calculate Total by specific column, then apply column total to each row

问题描述

我想应用总计数('case_id')，同时按项目分组.

I would like to apply the total count('case_id'), while grouping by the Item.

这是我之前的问题 DAX Measure 用于计算聚合数据，但按案例 ID 分组.这给了我 sub_item 的总计数('case_id').

This was my previous ask DAX Measure to calculate aggregate data, but group by Case ID. This gave me the total count('case_id') by sub_item.

Measure = 
VAR datesSelection = 
    DATE(
        YEAR(SELECTEDVALUE('Date Selection'[DateWoTime]))
        ,MONTH(SELECTEDVALUE('Date Selection'[DateWoTime]))
        ,DAY(SELECTEDVALUE('Date Selection'[DateWoTime]))
    )
VAR devicesTotal =
    CALCULATETABLE (
        VALUES ( Outages[Sub_Item] ),
        ALLSELECTED ( Outages ),
        Outages[DATE] >= datesSelection,
        VALUES ( Outages[Sub_Item] )
    )
var counts =
    CALCULATE (
        COUNT( Outages[CASE_ID] ),
        ALLSELECTED( Outages ),
        Outages[Sub_Item] IN devicesTotal
    )
return 
    counts

我收到了.

| Item  | Sub_Item | TYPE | Case ID    | Date             | Measure |
|-------|----------|------|------------|------------------|---------|
| 701ML | abc      | TFUS | 1312937981 | 7/16/19 7:18:00  | 1       |
| 702ML | abc      | TFUS | 1312958225 | 7/16/19 11:13:00 | 1       |
| 702ML | abc1     | TFUS | 1312957505 | 7/16/19 11:03:00 | 1       |
| 702ML | abc2     | TFUS | 1312954287 | 7/16/19 10:24:00 | 1       |
| 702ML | abc3     | TFUS | 1312938599 | 7/16/19 7:28:00  | 1       |
| 702ML | abc4     | TFUS | 1290599620 | 5/25/18 15:43:00 | 2       |
| 702ML | abc4     | TFUS | 1312950297 | 7/16/19 9:43:00  | 2       |
| 708BI | abc      | TFUS | 1312947288 | 7/16/19 9:13:00  | 1       |
| 712BI | abc      | TFUS | 1312944078 | 7/16/19 8:30:00  | 1       |
| 785DL | abc      | TFUS | 1312937536 | 7/16/19 7:12:00  | 1       |
| 786DL | abc      | TFUS | 1312992583 | 7/16/19 14:59:00 | 1       |
| 791DI | abc      | LFUS | 1289094627 | 4/28/18 20:07:00 | 2       |
| 791DI | abc      | LFUS | 1312958972 | 7/16/19 11:17:00 | 2       |
| 791DI | abc1     | LFUS | 1313005237 | 7/16/19 14:00:00 | 2       |
| 791DI | abc2     | RCLR | 1290324328 | 5/22/18 15:36:00 | 2       |
| 841JU | abc      | TFUS | 1312955016 | 7/16/19 10:32:00 | 1       |
| 841JU | abc1     | SBKR | 1288688911 | 4/15/18 10:09:56 | 2       |
| 841JU | abc1     | SBKR | 1312961007 | 7/16/19 11:46:24 | 2       |
| 871NI | abc2     | TFUS | 1304308511 | 3/24/19 19:13:00 | 2       |
| 871NI | abc      | TFUS | 1313015455 | 7/16/19 18:39:00 | 2       |
| 917CN | abc      | TFUS | 1312945831 | 7/16/19 8:58:00  | 1       |
| 918CN | abc      | LFUS | 1292611263 | 6/30/18 9:41:00  | 2       |
| 918CN | abc      | LFUS | 1313006283 | 7/16/19 17:03:00 | 2       |
| 922DU | abc      | TFUS | 1312987081 | 7/16/19 14:20:00 | 1       |
| 922DU | abc1     | TFUS | 1313005803 | 7/16/19 17:04:00 | 1       |
| 922DU | abc2     | TFUS | 1313003541 | 7/16/19 16:42:00 | 1       |
| 931LF | abc      | TFUS | 1312972165 | 7/16/19 12:46:00 | 1       |

当我想得到这个时.

| Item  | Sub_Item | TYPE | Case ID    | Date            | Measure |
|-------|----------|------|------------|-----------------|---------|
| 701ML | abc      | TFUS | 1312937981 | 7/16/2019 7:18  | 1       |
| 702ML | abc      | TFUS | 1312958225 | 7/16/2019 11:13 | 6       |
| 702ML | abc1     | TFUS | 1312957505 | 7/16/2019 11:03 | 6       |
| 702ML | abc2     | TFUS | 1312954287 | 7/16/2019 10:24 | 6       |
| 702ML | abc3     | TFUS | 1312938599 | 7/16/2019 7:28  | 6       |
| 702ML | abc4     | TFUS | 1290599620 | 5/25/2018 15:43 | 6       |
| 702ML | abc4     | TFUS | 1312950297 | 7/16/2019 9:43  | 6       |
| 708BI | abc      | TFUS | 1312947288 | 7/16/2019 9:13  | 1       |
| 712BI | abc      | TFUS | 1312944078 | 7/16/2019 8:30  | 1       |
| 785DL | abc      | TFUS | 1312937536 | 7/16/2019 7:12  | 1       |
| 786DL | abc      | TFUS | 1312992583 | 7/16/2019 14:59 | 1       |
| 791DI | abc      | LFUS | 1289094627 | 4/28/2018 20:07 | 4       |
| 791DI | abc      | LFUS | 1312958972 | 7/16/2019 11:17 | 4       |
| 791DI | abc1     | LFUS | 1313005237 | 7/16/2019 14:00 | 4       |
| 791DI | abc2     | RCLR | 1290324328 | 5/22/2018 15:36 | 4       |
| 841JU | abc      | TFUS | 1312955016 | 7/16/2019 10:32 | 3       |
| 841JU | abc1     | SBKR | 1288688911 | 4/15/2018 10:09 | 3       |
| 841JU | abc1     | SBKR | 1312961007 | 7/16/2019 11:46 | 3       |
| 871NI | abc2     | TFUS | 1304308511 | 3/24/2019 19:13 | 2       |
| 871NI | abc      | TFUS | 1313015455 | 7/16/2019 18:39 | 2       |
| 917CN | abc      | TFUS | 1312945831 | 7/16/2019 8:58  | 1       |
| 918CN | abc      | LFUS | 1292611263 | 6/30/2018 9:41  | 2       |
| 918CN | abc      | LFUS | 1313006283 | 7/16/2019 17:03 | 2       |
| 922DU | abc      | TFUS | 1312987081 | 7/16/2019 14:20 | 3       |
| 922DU | abc1     | TFUS | 1313005803 | 7/16/2019 17:04 | 3       |
| 922DU | abc2     | TFUS | 1313003541 | 7/16/2019 16:42 | 3       |
| 931LF | abc      | TFUS | 1312972165 | 7/16/2019 12:46 | 1       |

推荐答案

您需要指定在度量中聚合的级别.目前，您在 Sub_Item 级别进行聚合.

You need to specify what level you are aggregating at in your measure. Currently, you are aggregating at the Sub_Item level.

要在 Item 级别进行聚合，只需将度量中的 Sub_Item 替换为 Item.

To aggregate at the Item level, simply replace Sub_Item with Item in your measure.

这篇关于按特定列计算总计，然后将列总计应用于每一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！