Grouping values

DAX DAX Calculations
1

Hello,

Thank you for taking the time to read my request. I’m new to PowerBI and trying I’m to replicate some reports in Power BI using Dax

Basically, I’m trying to group YTD, MTD, Today in a specific format.

MY first idea was to use the row function and then use the union function to join them together but this seems to be the wrong approach. Any ideas on the approach would be welcomed.

Excel File and data attached

Thank you
DNAsampleData.xlsx (18.2 KB)

Best regards
Ant

2

Hello @Tones,

Thank You for posting your query onto the Forum.

To achieve the results into the specific format I created a table. Below is the screenshot provided for the reference -

Grouping Table

Now, I created the 4 measures one each for Total Value, Total Costs, Margin and Margin %. Below are the measures provided for the reference -

Total Value = 
VAR _Current_Item = SELECTEDVALUE( 'Grouping Table'[Grouping] )

VAR _Total_Value_YTD = 
CALCULATE( SUM( Data[Value] ) , 
    DATESYTD( Dates[Date] ) )

VAR _Total_Value_MTD = 
CALCULATE( SUM( Data[Value] ) , 
    DATESMTD( Dates[Date] ) )

VAR _Total_Value_Today = 
CALCULATE( SUM( Data[Value] ) , 
    FILTER( ALLSELECTED( Dates ) ,
        Dates[Date] = MAX( Data[Date] ) ) )

RETURN
SWITCH( TRUE() ,
    _Current_Item = "YTD" , 
        IF( ISBLANK( _Total_Value_YTD ) , 0 , _Total_Value_YTD ) , 
    _Current_Item = "MTD" , 
        IF( ISBLANK( _Total_Value_MTD ) , 0 , _Total_Value_MTD ) ,
    _Current_Item = "Today" , 
        IF( ISBLANK( _Total_Value_Today ) , 0 , _Total_Value_Today ) ,
0 )


Total Cost = 
VAR _Current_Item = SELECTEDVALUE( 'Grouping Table'[Grouping] )

VAR _Total_Cost_YTD = 
CALCULATE( SUM( Data[Cost] ) , 
    DATESYTD( Dates[Date] ) )

VAR _Total_Cost_MTD = 
CALCULATE( SUM( Data[Cost] ) , 
    DATESMTD( Dates[Date] ) )

VAR _Total_Cost_Today = 
CALCULATE( SUM( Data[Cost] ) , 
    FILTER( ALLSELECTED( Dates ) , 
    Dates[Date] = MAX( Data[Date] ) ) )

RETURN
SWITCH( TRUE() ,
    _Current_Item = "YTD" , 
        IF( ISBLANK( _Total_Cost_YTD ) , 0 , _Total_Cost_YTD ) , 
    _Current_Item = "MTD" , 
        IF( ISBLANK( _Total_Cost_MTD ) , 0 , _Total_Cost_MTD ) ,
    _Current_Item = "Today" , 
        IF( ISBLANK( _Total_Cost_Today ) , 0 , _Total_Cost_Today ) ,
0 )

Margin = [Total Value] - [Total Cost]

Margin % = DIVIDE( [Margin] , [Total Value] , 0 )

Finally, this is how the final results will look like. Below is the screenshot provided for the reference -

Final Results
Final Results815×394 12.4 KB

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Note: You can modify the formulas/measures as per your requirements. I’ve created them as per the provided template.

Thanks and Warm Regards,
Harsh

Grouping Values - Harsh.pbix (71.8 KB)

3 Likes
3

Thank you very much for this I was really stuck here this has taken a lot of pressure off.

All the best

4

Hello @Tones,

You’re Welcome. :slightly_smiling_face:

I’m glad that I was able to assist you.

Thanks and Warm Regards,
Harsh