SUMPRODUCT with multiple filter in DAX

[Anonymous]

 Hi Experts,

Please hava a look, Is it possible to convert the bellow formula into DAX,

=IFERROR(SUMPRODUCT(--(Table1[Date]=$B44),--(Table1[Col1]=$B$1),Table1[Col2],Table1[Col3])/SUMPRODUCT(--(Table1[Date]=$B44),--(Table1[Col1]=$B$1),Table1[Col3]),NA())

Regrads,

K.M.S.S

[v-jiascu-msft]

Hi @Anonymous,

Did it help? Can you mark it as an answer?

Best Regards,

Dale

[v-jiascu-msft]

Hi @Anonymous,

Can you share a sample of your data? The pbix file would be great. We can't write a DAX formula without data. 

Best Regards,

Dale

[Anonymous]

Hi Dale,

Thanks for your quick response.

Please find attached excel file:

https://drive.google.com/file/d/1CqjoexLq323bofzRyphDrPME8ux52vvX/view?usp=sharing

https://drive.google.com/file/d/1CqjoexLq323bofzRyphDrPME8ux52vvX/view?usp=sharing

I could not export the excel file data into PBI, as the file's data tab is interlinked with Table11 tab's data with Pivot table.

Please help me to create the complete tables in PBI with DAX.

Regards,

Salem

[v-jiascu-msft]

Hi @Anonymous,

Please check out the demo in the attachment.

1. It seems the formula isn't consistent with your data. So I can't build the DAX formula. Can you make it more clear, please?

2. We can import the excel data by the connector "Excel".

3. The table Data could be as follows. (I can't find "C11" and "C22" neither.)

Data =
SUMMARIZE (
    'Table1',
    Table1[COL5],
    "sum of c11", SUM ( 'Table1'[COL11] ),
    "sum of c22", SUM ( Table1[COL12] )
)

Best Regards,

Dale