Need a assistance with DAX calculation

[MarshalSK]

Hi Folks, Need your experties, for DAX formula to get Sum of sales based on Col2.

I have provided Data, screen shot in Expected result (without filter and with Filter )

Sample Data:

MonthYear	Col1	Col2	Col3	Col4	Sales
Jan-23	Pro1	Sub Pro1	A1	Z1	100
Jan-23	Pro1	Sub Pro1	A2	Z1	150
Feb-23	Pro2	Sub Pro1	B4	Y1	200
Mar-23	Pro3	Sub Pro1	B3	Y2	250
Jan-23	Pro1	Sub Pro2	B1	Z1	300
Jan-23	Pro1	Sub Pro3	C1	Z1	350
Jan-23	Pro1	Sub Pro3	C2	Y1	180

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

[MarshalSK]

Hi @Ahmedx , Thanks for the response and sorry expected result is slightly different which i missed out.

Sum of Sales should be based on Col1+Col2 (Expcted output as below screen shot)

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[MarshalSK]

Hi @Ashish_Mathur  my screen shots from excel and @Ahmedx  pbi file is pretty close, you can use the same . Thanks.

also let me know if anything specific you need to help me.

[Ashish_Mathur]

Hi,

Try this measure

m3 = CALCULATE([m1], ALLSELECTED(),GENERATE(VALUES('Table'[Col1]),VALUES('Table'[Col2])))

I have removed the criteria of Flag in the filter pane.

Hope this helps.

[MarshalSK]

@Ashish_Mathur . really helpful. how to include two more columns in m3 ?

m3 = CALCULATE([m1], ALLSELECTED(),GENERATE(VALUES('Table'[Col1]),VALUES('Table'[Col2])))

GENERATE(VALUES('Table'[Col1]),VALUES('Table'[Col2]))

[Anonymous · Accepted solution]

Hi @MarshalSK ,

I suggest you to try code as below to create a measure.

Sum of Sales =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[MonthYear] = MAX ( 'Table'[MonthYear] )
            && 'Table'[Col1] = MAX ( 'Table'[Col1] )
            && 'Table'[Col2] = MAX ( 'Table'[Col2] )
    )
)

Result is as below.

Without Filter

Filter -> Exclude ->Col4-Z1

Filter -> Exclude ->Col4-Y1,Col3-B4

Best Regards,
Rico Zhou

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

[Ashish_Mathur]

Good to know that.  You may write multiple GENERATE() functions but that would be cumbersome.  Someone else will help you with a better solution. 

[MarshalSK]

Thanks for the response. M2 should be 450 aggregation should happen on top of Col2. Results are not matching with screen shot.

Summary Table

(Filter -> Exclude -> Col4-Z1)

[Ahmedx]

pls try this

[PijushRoy]

Hi @MarshalSK 

Are you looking for a Dynamic title as per the FIlter selection?

If you looking for visual, please try Field Parameter
https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

Thanks
Pijush
Linkedin

[MarshalSK]

hi @PijushRoy , dynamic DAX formula to get Sum of sales, column based on Col2.