Calculate a distribution with a measure

[akb989]

Hi everyone,

as per the title we want to calculate a distribution of planned sales per calendar week. Here is a simplified version of the table called ‘data’:

We have different product groups and within each group there are different products with a product ID. Each product is sold for a certain amount of time indicated by the start and end date column. For the sale period we have an estimated number of sales which can be broken down into sales per week.
The goal is to calculate the sum of the sales according to the calendar week of each product but summarized by the product group like so:

So for example in product group B none of the product are sold in calendar week 31 and 32 of 2023, and for the following calendar weeks only the product B8 is sold. So therefor in the matrix visual the sales per calendar week of only product B8 is shown.

How can we achieve this with a measure?

Thanks in advance!

[Anonymous]

Hi @akb989 ,

Based on the information you provided, you can follow these steps:

1.Add new table.

Table 2 = VALUES(data[Column])

2.Add new measure.

Measure =

VAR _a =

    CALCULATE (

        MAX ( 'data'[Sales per Week] ),

        FILTER (

            'data',

            [Product Group] = "A"

                && [Column] = SELECTEDVALUE ( 'Table 2'[Column] )

        )

    )

VAR _b =

    CALCULATE (

        MAX ( 'data'[Sales per Week] ),

        FILTER (

            'data',

            [Product Group] = "B"

                && [Column] = SELECTEDVALUE ( 'Table 2'[Column] )

        )

    )



VAR result =

    CALCULATE (

        MAX ( 'data'[Sales per Week] ),

        FILTER (

            ALLSELECTED ( 'data' ),

            'data'[Product Group]

                IN VALUES ( 'data'[Product Group] )

                    && 'data'[Column] IN VALUES ( 'Table 2'[Column] )

        )

    )

VAR _answer_ =

    IF ( result <> BLANK (), result, 0 )

RETURN

    IF ( ISINSCOPE ( 'data'[Product Group] ), _answer_, _b - _a )

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

^{Best Regards,}

^{Ada Wang}

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

[akb989 · Accepted solution]

Hi Ada, 
thank you for your response! Unfortunately it wasn't quite the outcome I wanted but I solved it in a differnet way by adding new columns to the data table for each calendar week and summing them with a measure. 😊