Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anupal
Frequent Visitor

Need help with DAX

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 2Product 3Total  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)     
      
A7000000    
B300000    
Final Cal4%    

 

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

 

Thanks

AP

1 ACCEPTED SOLUTION

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.

 

Picture2.png

 

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" )
)
 
 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


View solution in original post

4 REPLIES 4
Anupal
Frequent Visitor

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

AP

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.

 

Picture2.png

 

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" )
)
 
 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


Hello Jihwan,

 

Thanks for the help. This worked 🙂

Jihwan_Kim
Super User
Super User

Hi,

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

 

Picture1.png

 

Link to the sample pbix file 

 

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.