Forum Discussion

manideep547's avatar
manideep547
Icon for Helper III rankHelper 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          Amo...
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

manideep547's avatar
manideep547
Icon for Helper III rankHelper III
6 years ago

amitchandak for direct query can we use a new calculated column?
if I used column based on the slicer the count should be changed ??

zoloturu's avatar
zoloturu
Icon for Memorable Member rankMemorable Member
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

  • manideep547's avatar
    manideep547
    Icon for Helper III rankHelper III
    6 years ago

    condition column is not supported indirect query mode. And for some of the table, I have varchar and some in decimal but I have to compare with varchar (AED 0, AED1-50..etc) 

  • zoloturu's avatar
    zoloturu
    Icon for Memorable Member rankMemorable Member
    6 years ago

    manideep547 ,

     

    Here is an updated measure which is handling a case when you select zero at slicer.

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

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
    )
VAR __result = __tbl_A + __tbl_B + __tbl_C + __tbl_D

RETURN
    IF ( __result > 0, __result, 0 )

     
    Regards,
    Ruslan Zolotukhin (zoloturu)
    BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
    -------------------------------------------------------------------
    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