Forum Discussion

manideep547's avatar
manideep547
Helper III
6 years ago
Solved

Amount breakdown

 Here I have to breakdown the Amount like(AED 0,AED 1-50,AED 51-100, AED 101-200..etc).
 
    For example in a slicer, I clicked AED 1-50 .IN card the count should be 4.
Table A  
Id          Amount 
1             10
2              50
Table B 
ID   Amount
3         101
4          200
Table C
ID  Amount
5        5
6         25
Table D
ID Amount 
7      70
8      80
NOTE: If possible  we should not use any relationship in my case it will conflict with another 

Need Help
  • zoloturu's avatar
    zoloturu
    6 years ago

    Hi manideep547,

     

    You can achieve it using a measure:

     

    Measure = 
VAR __breakdown = SELECTEDVALUE ( Breakdown[Amount breakdown] )
VAR __delimiter_position = FIND ( "-", __breakdown, 1, 0 )
VAR __value_from = VALUE ( LEFT ( __breakdown, __delimiter_position - 1 ) )
VAR __value_to = VALUE ( RIGHT ( __breakdown, LEN ( __breakdown ) - __delimiter_position ) )

VAR __tbl_A =
    CALCULATE (
        COUNTROWS ( 'Table A' ),
        'Table A'[Amount] >= __value_from,
        'Table A'[Amount] <= __value_to
    )
VAR __tbl_B =
    CALCULATE (
        COUNTROWS ( 'Table B' ),
        'Table B'[Amount] >= __value_from,
        'Table B'[Amount] <= __value_to
    )
VAR __tbl_C =
    CALCULATE (
        COUNTROWS ( 'Table C' ),
        'Table C'[Amount] >= __value_from,
        'Table C'[Amount] <= __value_to
    )
VAR __tbl_D =
    CALCULATE (
        COUNTROWS ( 'Table D' ),
        'Table D'[Amount] >= __value_from,
        'Table D'[Amount] <= __value_to
    )
RETURN
    __tbl_A + __tbl_B + __tbl_C + __tbl_D

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Regards,
    Ruslan Zolotukhin (zoloturu)
    BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
    -------------------------------------------------------------------
    Did I answer your question? Mark my post as a solution!
    It was useful? Press Thumbs Up!

    You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups

7 Replies