Forum Discussion

deb_power123's avatar
deb_power123
Icon for Helper V rankHelper V
5 years ago

Get the column difference after filter selection

Hello Everyone

 

I need the DAX measure expression for the below scenario.

 

For my table Sales, I have 4 columns-Quateryear, SalesAmount-A, SalesAmount-B,Place.

 

Both the  column values of SalesAmount-A and  SalesAmount-B are same and they have  filters A and filter B applied on SalesAmount-A and  SalesAmount-B respectively. filter A and filter B has Quateryear values.

 

so as per my requirement if I select any quateryear value in filterA, it should filter and sum the Sales AmountA  and if it select any quateryear value in filter B, it should filter and sum the SalesAmountB. Based on these filtered values selection, I should get the difference between SalesAmount -A and Sales Amount-B columns in the DifferenceSales

 

P.S: Here the column values for SalesAmount-A, SalesAmount-B are the same/you can say duplicate column. The filters too refers to the same column quateryear.

 

I wrote a measure to handle this but it is not working.

 

Could someone suggest some changes.

 

SalesAmount difference from Q12017, Q22017, Q32017, or Q42017 =
VAR __SALES_AMOUNTA =
    CALCULATE(
        SUM('Sales'[SalesAmount-A]),
        'Sales'[QuaterYear]
            IN { "Q12017", "Q22017", "Q32017", "Q42017" }
    )
VAR __SALES_AMOUNTB =
    CALCULATE(
        SUM('Sales'[SalesAmount-B]),
        'Sales'[QuaterYear]
            IN { "Q12017", "Q22017", "Q32017", "Q42017" }
    )
RETURN
    IF(NOT ISBLANK(__SALES_AMOUNTA), __SALES_AMOUNTB - __SALES_AMOUNTA)
 
SOURCE INPUT:excel
QuaterYearSalesAmount-ASalesAmount-BPlaceDifferenceSales
Q1201710001000London 
Q1201711001100London 
Q1201711201120London 
Q12017900900Tokyo 
Q2201722002200Tokyo 
Q2201721002100Tokyo 
Q2201710001000Tokyo 
Q22017500500Tokyo 
Q2201719001900Tokyo 
Q2201719001900Tokyo 
Q2201720002000Tokyo 
Q2201740004000Tokyo 
Q2201721002100Tokyo 
Q3201711001100Kaula Lampur 
Q32017890890Kaula Lampur 
Q4201722002200Berlin 
Q4201723002300Berlin 
Q4201729002900Berlin 

 

 

Expected Output in PowerBI should look like this:[Just for look and feel I made a similar in excel as below]

 

My Sales Matrix table visualization :

Need Help DAX
Need Help DAX measure

1 Reply

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    Where is the Tokyo value for Q12017?

     

    You probably need to duplicate your data table, and use one copy for the A filter and the other copy for the B filter. Then use a measure to calculate the difference between the selected items. You will need to lower your expectations on the output format, it is not possible to implement it exactly as in your example.