SumIf

[kevbrown1980]

Hi 

I'm looking to convert a Qlikview Dashboard into PowerBI, not my easiest project.

I have a calculation which is Sum(if in Qlikview but I'm not sure how to recreate it in PowerBI. Can someone help? The measure in Qlikview is - 

Sum(if([Level 5 Expense Description] = 'Contract Income - Fixed', [Budget - YTD], [Actual - YTD]))

How can I convert to PowerBI?

Thanks

[v-veshwara-msft]

Hi @kevbrown1980 ,
Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out. Consider sharing a sample PBIX file or a small sample dataset.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community 
Thank you.

[v-veshwara-msft · Accepted solution]

Hi @kevbrown1980 ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out and consider sharing a sample PBIX file or a small sample dataset so that we can reproduce the issue from our end and guide more accurately.
Thank you.

[ryan_mayu]

@kevbrown1980 

pls  provide some sample data and expected output.

[v-veshwara-msft]

Hi @kevbrown1980 ,

Just checking in to see if you query is resolved and if any responses were helpful.
Otherwise, feel free to reach out for further assistance.

Thank you.

[v-veshwara-msft]

Hi @kevbrown1980 ,

Thanks for sharing the scenarion in Microsoft Fabric Coimmunity.

I tried reproducing this using some sample data structured like your scenario, where the expense description is in a dimension table and the YTD values are in a related fact table. I used a measure like below and it’s working at my side:

YTD Total = 
SUMX (
    Fact_Financials,
    IF (
        RELATED ( Dim_Account[Level 5 Expense Description] ) = "Contract Income - Fixed",
        Fact_Financials[Budget - YTD],
        Fact_Financials[Actual - YTD]
    )
)

 Please make sure there is a relationship between Dimension and FactTable based on the appropriate key column.

If you're still facing errors, please consider sharing a sample PBIX file or a small sample dataset so that we can reproduce the issue from our end and guide more accurately.

Hope this helps. Please reach out for further assistance.

Thank you.

Please find the attached .pbix for reference.
Also thanks to @mdaatifraza5556  and @bhanu_gautam for your help.

[mdaatifraza5556]

Hi @kevbrown1980 

Can you please try the below ?

Make sure it works when a relationship exists between the two tables.

Total Amount :=
SUMX (
Table2,
IF (
RELATED ( Table1[Level 5 Expense Description] ) = "Contract Income - Fixed",
Table2[Budget - YTD],
Table2[Actual - YTD]
)
)


If this answers your questions, kindly accept it as a solution and give kudos.

[kevbrown1980]

Sadly it doesn't like that 

[bhanu_gautam]

@kevbrown1980  Try using

DAX
Measure =
SUMX(
YourTableName,
IF(
YourTableName[Level 5 Expense Description] = "Contract Income - Fixed",
YourTableName[Budget - YTD],
YourTableName[Actual - YTD]
)
)

[kevbrown1980]

It looks as though this won't work as I'm working from 2 different table. [Level 5 Expense Description] from one table and [Budget - YTD] and [Actual - YTD] from another