topic Re: Need help with DAX in DAX Commands and Tips

Need help with DAX

Anonymous — Thu, 19 Aug 2021 08:16:00 GMT

Hello Experts,

I am new to Power BI DAX and still finding my feet. I am looking some help in creating a DAX for the below Requirment-

		Product 1	Product 2	Product 3	Total Rev
Customer 1		$ 100,000	$ 2,000,000	$ 5,000,000	$ 7,100,000
Customer 2		$ 200,000	$ 5,000,000		$ 5,200,000
Customer 3		$ 500,000			$ 500,000
Customer 4		$ 500,000		$ 1,000,000	$ 1,500,000
Customer 5		$ -	$ 4,000,000		$ 4,000,000
Total		$ 1,300,000	$ 11,000,000	$ 6,000,000	$ 18,300,000

Step 1: Isolate customers that purchased both Prod 1 and Prod 2 in the same quarter.
Step 2: Find sum of Prod 2 revenue for customers in Step 1. This will be "A."
Step 3: Find sum of Prod 1 revenue for customers in Step 1. This will be "B."
Final Cal- B/(A+B)

A	7000000
B	300000
Final Cal	4%

So I am looking to get the DAX for Step 1 and Step 2. Any Help will be really Appreciated.

Thanks

Jihwan_Kim — Thu, 19 Aug 2021 08:55:06 GMT

Hi,

I am not sure how your data model looks like, but I hope you can make use of the below sample pbix file.

Anonymous — Thu, 19 Aug 2021 09:58:17 GMT

Hello Jihwan,

Thanks for your response. However I am unable to download the Attached PBI file due to compatibilty issues,

Would It be possible for you to share the DAX of the KPI that the file has please.

Thanks

Jihwan_Kim — Thu, 19 Aug 2021 11:26:47 GMT

Hi,

I am not sure that how writing the measures here would help without opening & seeing the sample pbix file.

I tried to screen capture all the related information in order to make the below measures possibly work.

Rev total : =

SUM(Sales[Rev])

Rev total for customers both P1 & P2 : =

VAR _product1 =

CALCULATE ( [Rev total :], Products[Product] = "Product 1" )

VAR _product2 =

CALCULATE ( [Rev total :], Products[Product] = "Product 2" )

RETURN

CALCULATE ( [Rev total :], FILTER ( Sales, _product1 <> 0 && _product2 <> 0 ) )

Rev P1 & P2 total fix : =

SUMX(VALUES(Customers[Customer]), [Rev total for customers both P1 & P2 :])

Final Cal : =

DIVIDE (

CALCULATE ( [Rev P1 & P2 total fix :], Products[Product] = "Product 1" ),

CALCULATE ( [Rev P1 & P2 total fix :], Products[Product] = "Product 2" )

)

Anonymous — Tue, 24 Aug 2021 14:40:14 GMT

Hello Jihwan,

Thanks for the help. This worked 🙂