Need support on PBI DAX

[Anonymous]

Hi all

Need your support on POWERBI DAX.

I am converting Tableau to PowerBI. 

Below is the Tableau calculation and I am trying in PBI DAX. can anyone please help me out.

1) Scenario 1

IF (NOT ISNULL([Colomn1]) OR [Colomn1] <> 0)
AND ISNULL([Colomn2])
THEN 0 ELSE ([[Colomn2])
END

I have written below DAX calculation, but not sure.

IF((NOT(ISBLANK([Colomn1]) || [Colomn1 <> 0]))

&& ISBLANK([Colomn2]),0,[Colomn2])

please correct me, if i am wrong.

2) 1) Scenario 2

SUM(IFNULL([Colomn 1],0)) - SUM([Colomn 5])

I have written below DAX calculation, but not sure.

COlomn X = IF(ISBLANK([Colomn 1]),0,[Colomn 1])

SUM([Colomn X]- SUM(Colomn 5])

please correct me.

thanks in advance

[v-yuta-msft · Accepted solution]

@Anonymous ,

Your dax formulas in both senarios are correct. In addtion, you don't need to add a new column [Column X] in senario 2, you can use dax like below:

Result =
SUMX ( FILTER ( Table, Table[Colomn 1] <> BLANK () ), [Column 1] )
    - SUM ( [Colomn 5] )

Community Support Team _ Jimmy Tao

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

[Anonymous]

Hi @v-yuta-msft ,

Thank you very much for your support,

Still my end results are incorrect, below is the final calculation to find whether it is increased or decreased based on senario 2 result. 

senario 2 Result = SUMX ( FILTER ( Table, Table[Colomn 1] <> BLANK () ), [Column 1] ) - SUM ( [Colomn 5]

Tableau Calculation

IF { FIXED [Project ID] : ([senario 2 result]) } > 0 THEN "Increased"
ELSEIF { FIXED [Project ID] : ([senario 2 result]) } < 0 THEN "Decreased"
ELSEIF { FIXED [Project ID] : ([senario 2 result)]) } = 0 THEN "No Change"
ELSE NULL
END

PBI DAX

Variance = CALCULATE( [senario 2 result)],ALLEXCEPT([Project ID]))
Increased or Decreased Variance = IF([Variance]>0,"increased",IF([Variance]<0,"Decreased",IF([Variance]=0,"No change",Blank())))

Final output should be as below in Table green and Table red is what i am getting in PBI

Thank you in advance.

Regards,

Murali

[Cmcmahan]

So looking at your measures and output, it should be working. 
Assuming you're outputting [Variance] below the category, your DAX looks to be right.  You're getting the right values, but Project B is showing up in the wrong category.  Is [Increased or Decreased Variance] a measure or calculated column?  What visualization are you using to create the incorrect table?

Is it possible for you to anonymize a data sample and share it? Or even a sample .pbix?  

[Anonymous]

@Cmcmahan  Thank you very much.

I have created calculated column for increased or decreased variance using senario 2 Result (in the sheet Below) and senario 2 Result is a calculated measure.

I have used Matrix visual to show case

Sorry i dont have access to send doc , so sending you dummy data in text format.

Project ID	senario 2 Result	Categorisation
KALLI331834	939842	XE1
KALLI331845	365377	EN1
KALLI333955	331386	EN1
KALLI333963	116971	EN1
KALLI333967	91700	EN1
KALLI331163	78474	XE1
KALLI333768	53712	XE1
KALLI333491	49999	XE1
KALLI333461	33392	IFN
KALLI333653	33111	XE1
KALLI333966	33464	EN1
KALLI333964	31435	EN1
KALLI333815	31116	IFN
KALLI333977	37972	EN1
KALLI333598	10000	IFN
KALLI333496	8112	XE1
KALLI331791	6692	XE1
KALLI333736	6315	XE1
KALLI333961	5199	EN1
KALLI331747	3582	IFN
KALLI333976	3134	EN1
KALLI333114	1111	EN1
KALLI333493	-3750	AC1
KALLI333181	-13319	XE1
KALLI333467	-35355	AC1
KALLI331771	-31191	AC1
KALLI333347	-31465	XE1
KALLI119717	-88151	AC1
KALLI333741	-91887	XE1
KALLI333656	-135396	XE1
KALLI331953	-153874	AC1
KALLI331851	-165862	AC1
KALLI331733	-337887	AC1
KALLI333941	-337939	AC1

Once again thank you very much for responding 

Thanks,

Murali

[Cmcmahan]

So, literally copy/pasting your data and measure, I was able to create this which seems to work as you intended:

So at this point I'm curious about your original tables.  How do you have 2 different values for Category A?  Are there multiple categories within the same ProjectID?  What data did you use to create those original tables since my numbers are much larger, but I also don't have the raw data to calculate [scenario 2] myself.

[Anonymous]

@Cmcmahan 

Can you please remove the below Project ID's ( values and categories should remain ) from the source.

KALLI333955

KALLI333653

KALLI333598

KALLI333656

KALLI333941

Because i will be using this visual as a filter, so that detail chart will pop me which are the projects are increased and decreased and their categories accordingly 

can you please share me the .pbix file.

Thanks,

Murali

[Anonymous]

@CmcmahanWow brilliant

This is what exactly i wanted but, i still getting incorrect values. 

I don't see you using the Project ID information, Project ID should be fixed and then you need to calculate the variance.

hope you have used similar below calculation to create variance.

Variance = CALCULATE( [senario 2 result)],ALLEXCEPT([Project ID]))
Increased or Decreased Variance = IF([Variance]>0,"increased",IF([Variance]<0,"Decreased",IF([Variance]=0,"No

change",Blank())))

Senario 2 is difference of two fields, pretty straight .

can you please share me .pbix file, so that i will help me to understand better.

There can be projects where they are decreased due to any reason and some projects would have performed well and those are in increased bucket. 

The project status could be multiple categories ( stages ) and those projects can be present in decreased bucket and increased bucket too

Numbers are dummy.

Thank you very much

Thanks,

Murali

[Cmcmahan]

I can't calculate scenario 2 result myself, since I don't have raw data real or fake. 

In the data you provided, each [Scenario 2 result] is already associated with a Project ID.  The difference between your [Scenario 2 result] and [Variance] is that [Variance] is [Scenario 2 result] where the Project ID is the same. Since all the project IDs are distinct, I just renamed [Scenario 2 result] to [Variance].  I could set up the exact same measure you have for [Variance] and this would still work identically.

I grouped them by category, since the table you showed doesn't have any ProjectID groupings.  The values in the matrix are the SUM of all values in that category. Removing the project IDs changes nothing, since those values are still in the category buckets. If you can provide sample raw data so I can start from square one, or a screenshot of your current visual and the associated fields, I can help further.  Also, if the example data had projects with multiple categories, that would be great too.

EDIT: Here's the matrix if I set up rows split by project:

[Anonymous]

Hi @Cmcmahan :

Thank you very much for your extended support and connecting with me offline.

Appreciate your dedication towards resolving the issue.