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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Chanleakna123
Post Prodigy
Post Prodigy

Column Calculation not get right

Hi All , 

 

I have such below Table , Column of Purchase/No PUrchase ; Count are calculated in Edit Query , as of result this is the table  : 

Customer Code Customer Name DateValue Purchase/No Purchase Count
12345Abc1/1/201912Purchase1
12345Abc2/1/201923Purchase1
12345Abc3/1/20190No Purchase 0
45678xyz1/1/201912Purchase1
45678xyz2/1/201923Purchase1
45678xyz3/1/20193Purchase1

 

Expected Result : 

I wanna calculated which customer who re purchased and Non : 

 

If Count >=3 , Repurchased , 

If Count < 3 , No Repurchased , 

I have calculated below columns : 

 

RE-Purchased = IF('Energy Drink'[Count on Purchased]>=RELATED('Master Outlet'[Count Outlet]),"Repurchased","No Repurchased") 
but the result turn all to No Repurchased 
Customer Code Customer Name Value Purchase/No Purchase CountRepurchased/No Repurchased
12345Abc35Purchased 2No Repurchased
45678xyz38Purchased 3Re Purchased 

1.PNG

1 ACCEPTED SOLUTION

Hi @Chanleakna123 

 

Please check below dax.

Count = 
CALCULATE (
    COUNT ( 'Energy Drink'[Customer Name - Store host code] ),
    FILTER (
        ALLSELECTED ( 'Energy Drink' [Customer Name - Store host code]),
       [Repurchased/No Repurchased]= SELECTEDVALUE('Re-Purchased/No Repurchased'[Type])
    )
)

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

12 REPLIES 12
v-cherch-msft
Employee
Employee

Hi @Chanleakna123 

 

You may try to create a measure like below.If it is not your case,please show me the Master Outlet table.

RE-Purchased =
IF (
    SUM ( 'Energy Drink'[Count on Purchased] )
        >= MAX ( 'Master Outlet'[Count Outlet] ),
    "Repurchased",
    "No Repurchased"
)

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-cherch-msft  , hi the calculation works perfect , but can i have it in Column ? It will be easy to filter or slicer. of Repurchased or no Repurchased. 

Hi @Chanleakna123 

 

You may try below dax:

Column =
IF (
    CALCULATE (
        SUM ( 'Energy Drink'[count] ),
        ALLEXCEPT ( 'Energy Drink', 'Energy Drink'[Customer Code ] )
    )
        >= RELATED ( 'Master Outlet'[Count Outlet] ),
    "Repurchased",
    "No Repurchased"
)

Regards,

Cherie

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi , when i use Date Filter , it dones't work , Measure Repurchased/No Repurchased , and Colum are different. 

 

When i Filter "Date " , Measure- Repurchased/NoRepurchased filtered as expected , But Column - Column Calculation i used as per your mentioned was not run as date filter. you see it turns wrong . 

how to solve such thing ? I want it to be filtered any slicer i used For example : Date

 

 

1.PNG

Hi @Chanleakna123 

 

Only measure can be dynamic.If you create a column,it cannot be dynamic or changed by slicer.I would suggest you use measure instead of calculated column.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

hi @v-cherch-msft  ,if i acheived this by using Measure , i wanna count , How Many Re-purchased and No Repurchased . How to use the calculation ? 

Hi @Chanleakna123 

 

You may try create measure like below.If it is not your case,please share the .pbix file for us to test.You can upload the .pbix file to OneDrive and post the link here or send me via private message. Do mask sensitive data before uploading.

Repurchased Count =
CALCULATE (
    DISTINCTCOUNT ( 'Energy Drink'[Customer Code ] ),
    FILTER (
        ALLSELECTED ( 'Energy Drink'[Customer Code ] ),
        [RE-Purchased1] = "Repurchased"
    )
)

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-cherch-msft  , https://www.dropbox.com/s/79j0c729sa3khda/20191203-Category%20Report%20V3.pbix?dl=0

hi , you can go and check the link above , 

*My purpose is also to see how many Repurchased and No Repurchased. it is all filtered based on the data filtering. 

Hi @Chanleakna123 

 

Please check below dax.

Count = 
CALCULATE (
    COUNT ( 'Energy Drink'[Customer Name - Store host code] ),
    FILTER (
        ALLSELECTED ( 'Energy Drink' [Customer Name - Store host code]),
       [Repurchased/No Repurchased]= SELECTEDVALUE('Re-Purchased/No Repurchased'[Type])
    )
)

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi @v-cherch-msft , I do the calculation for another Purchase/No Purchase , And i follow the calculation of Repurchased/No Repurchased as per your mentioned. 

but it's strang , Purchase/No PUrchase Calculation : Count only 2 Customers Who Purchase , but for customer who get 0 it will go to No Purchased , but it doesn't count into No Purhcase , it keeps blank . therefore i can't see the number of customer who Purchase and No Purchase , Purchase is customer value >=1 , No Purchase <1 .

 
 
***Purchased/No Purchased = SWITCH(TRUE(),[Sum Value]>=1,"Purchased","No Repurchased")
 
***Count Purchased/No Purchased =
CALCULATE (
DISTINCTCOUNT ( 'Energy Drink'[Customer Name - Store host code] ),
FILTER (
ALLSELECTED ( 'Energy Drink' [Customer Name - Store host code]),
[Purchased/No Purchased]= SELECTEDVALUE('Purchased/No Purchased'[Type])
))
 
 

1.PNG

Can u please help to settle how to see the amount of Customer who purchase and no purchase on table ? While now no purchased customer show blank

Hi @v-cherch-msft  you're brilliant , thanks for your quick help. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.