Issue with Grouping in Power BI

[Baudi]

Hello everyone,

I’m still relatively new to Power BI and currently facing a conceptual issue. I’m working with two tables: Orders and CLE.

• The Orders table contains a week number field, which I want to group by, as well as a production date, representing the planned production date.
• The CLE table contains the individual bookings and a posting date, which indicates when the production was actually booked.

My goal is to analyze, for each week, how much was booked on the planned date (production date). My calculation works correctly for individual records, but as soon as I group by week, my measure always returns BLANK.

Does anyone have an idea what might be causing this issue or how I can resolve it?

Thanks in advance!

QuantityOnTime = SUMX(
        FILTER(
            'CLE', 
            'CLE'[Posting Date] = SELECTEDVALUE(Orders[Production Date])
        ), 
        'CLE'[Quantity]
    )

[Ashish_Mathur · Accepted solution]

Hi,

Try this approach

1. Create a Calendar Table with calculated column formulas for Year, Month name, Month number and Week number.  Sort the Month name by the Month number
2. Create a relationship (Many to One and Single) from the Date column of the 2 Fact tables to this Calendar table
3. To your visual, drag Year and Week number from the Calendar table
4. Write this measure

Measure = sum('cle'[Quantity])

Hope this helps.

[Anonymous · Accepted solution]

Thanks for the reply from Ashish_Mathur  and samratpbi , please allow me to add some more information:
Hi  @Baudi ,

You can join [Posting Date] and [Product Date] as a join relationship between two tables

This way whether you use the table CLE's [Quantity] directly or create a measure with the following content, it will display properly:

Measure =
SUMX('CLE',[Quantity])

Note: You need to click on visual to set the blank rows to be filtered from the side filters, because data that is not connected to the two tables will be aggregated in the blank rows.

Best Regards,

Liu Yang

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

[Ashish_Mathur · Accepted solution]

Hi,

Try this approach

1. Create a Calendar Table with calculated column formulas for Year, Month name, Month number and Week number.  Sort the Month name by the Month number
2. Create a relationship (Many to One and Single) from the Date column of the 2 Fact tables to this Calendar table
3. To your visual, drag Year and Week number from the Calendar table
4. Write this measure

Measure = sum('cle'[Quantity])

Hope this helps.

[samratpbi]

Hi, I can see you have relationship where Order table filtering CLE table. If you want to have Quantity from the CLE table, then simply create a below measure:

Total Qty = SUM(CLE[Quantity]).

Now if you bring your Week no, Production date along with the above measure, it should return the value correctly.

If this help to resolve your problem then please mark it as solution, thanks!

[Baudi]

Hello,

I can calculate the total sum, but in the end, I want to have three measures: "Early Quantity ", "On Time Quantity ", and "Late Quantity ". I'm not sure how to aggregate this correctly by week.