I can't create an aggregation table

[Anonymous]

Hi,

I have a datatable with daily about 2,000,000 records and need to keep the records of 32 consecutive dates in de Power BI data model.

The table is refreched every day: the earliest data disappears and the data of the most date is added.

I need to calculate for 5 different metrics, who contain either a 0 or a 1, the percentage of 1's compared to the total number of records, grouped by 3 different properties: Date, Type, Department.

In the same table there is extra detail data that will be shown in a table in the same Power PI.

F_Table:

PK
Date
Type

Department

Metric_01

Metric_02

Metric_03

Metric_04

Metric_05

Extra_Data_01

Extra_Data_02

Extra_Data_03

Extra_Data_04

Extra_Data_05

Extra_Data_06

Extra_Data_07

Extra_Data_08

Extra_Data_09

I like to make an aggregated table based on this table with group by Date, Type, Department,

Sum of Metric_01, Metric_02, Metric_03, Metric_04, Metric_05

And Count Line

Based on the aggregations a line graph is to be made for the 5 etric fields percentages per date

How do I make this aggregate Table.

The original table is imported (no Direct Query)

R.W.

Thanks

[Icey]

Hi @Anonymous ,

Please check if this is what you want:

1. Enter another table.

2. Create a measure like so:

Measure = 
SWITCH (
    TRUE (),
    SELECTEDVALUE ( Metric[Metric] ) = "Metric_01", DIVIDE (
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Metric_01] = 1
                    && 'Table'[Date] <= MAX ( 'Table'[Date] )
            )
        ),
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) )
        )
    ),
    SELECTEDVALUE ( Metric[Metric] ) = "Metric_02", DIVIDE (
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Metric_02] = 1
                    && 'Table'[Date] <= MAX ( 'Table'[Date] )
            )
        ),
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) )
        )
    ),
    SELECTEDVALUE ( Metric[Metric] ) = "Metric_03", DIVIDE (
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Metric_03] = 1
                    && 'Table'[Date] <= MAX ( 'Table'[Date] )
            )
        ),
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) )
        )
    ),
    SELECTEDVALUE ( Metric[Metric] ) = "Metric_04", DIVIDE (
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Metric_04] = 1
                    && 'Table'[Date] <= MAX ( 'Table'[Date] )
            )
        ),
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) )
        )
    ),
    SELECTEDVALUE ( Metric[Metric] ) = "Metric_05", DIVIDE (
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Metric_05] = 1
                    && 'Table'[Date] <= MAX ( 'Table'[Date] )
            )
        ),
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) )
        )
    )
)

3. Create a line chart.

For more details, please check the attached PBIX file.

Best Regards,

Icey

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

[Anonymous]

Thanks,

This was the sollution I already had more or less.
I wanted to use an aggregate table, so that on the moment we start using millions of records per date the measure wouldn't take too long.

R.W.

[amitchandak · Accepted solution]

Refer, if these can help

https://radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table

https://community.powerbi.com/t5/MBAS-Gallery/Aggregation-and-composite-model-Microsoft-Power-BI-fas...

https://docs.microsoft.com/en-us/power-bi/desktop-aggregations