Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Display ratio in row

Hi,   I want to display ratio in row. It likes the image following. But I only realized ratio in column. I created a measure following to calaculte ratio. Ratio % = DIVIDE(CALCULATE('Query...
General Comment
Interesting Links
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Ashish_Mathur 

     

    I realized my expected result following:

     

     

    Actual N (New) =
    IF(
        FIRSTNONBLANK('Table ldesc2'[ldesc2], 1) = "Ratio %" && NOT( HASONEFILTER(STAR_Detailed_group_PL[ldesc2]) ),
        FORMAT(
        CALCULATE(
            'Query1'[Actual N],
            FILTER(ALL('STAR_Agregated_group_PL'),'STAR_Agregated_group_PL'[ldesc2]="Net margin")
        )
        /
        CALCULATE(
            'Query1'[Actual N],
            FILTER(ALL('STAR_Detailed_group_PL'),'STAR_Detailed_group_PL'[ldesc2]="NET ACTIVITY TURNOVER")
        ), "00%"),
        [Actual N]
    )
v-xicai's avatar
v-xicai
Community Support
7 years ago

Hi Anonymous ,

 

Your issue is referred to the red part why it have data been calculated ? it should be blank as other sublevel rows, right?

 

Best Regards,

Amy

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi, v-xicai and Ashish_Mathur 

I created a measure following to calculate Net margin/NET ACTIVITY TURNOVER.


Measure 3 = IFERROR(IF(

                ISFILTERED('STAR_Detailed_group_PL'[ldesc2]) && NOT(ISFILTERED ('STAR_Account'[ldesc2])),

                'Query1'[Actual N]

                /

                CALCULATE(

                    'Query1'[Actual N],

                     FILTER('STAR_Detailed_group_PL','STAR_Detailed_group_PL'[ldesc2]="NET ACTIVITY TURNOVER")

            )

), BLANK())


But I got a ratio following:

But I don't know why NET ACTIVITY TURNOVER is calculated 100%. How to hide this 100%?

I am looking forward to hearing from you soon.

Best regards.

 

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi Anonymous ,

     

    How about the DAX below?

     

    Measure1= IF(MAX('STAR_Detailed_group_PL'[ldesc2])="NET ACTIVITY TURNOVER"&&,[Measure 3]<>BLANK(),BLANK(),[Measure 3])

     

    Best Regards,

    Amy

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-xicai ,

      Thank you  for your suggestion.

      I created a new measure using another suggestion:

       

      Ratio % = IFERROR(
                         IF(
                             NOT(HASONEVALUE(STAR_Detailed_group_PL[ldesc2])),
                             CALCULATE(
                                        'Query1'[Actual N],FILTER('STAR_Agregated_group_PL','STAR_Agregated_group_PL'[ldesc2]="Net margin"))
                                        /
                                         CALCULATE(
                                                    'Query1'[Actual N],FILTER('STAR_Detailed_group_PL','STAR_Detailed_group_PL'[ldesc2]="NET ACTIVITY TURNOVER")
                                                     )
                              ),
                          BLANK()
                          )
      I got the result following.
       
       
      Best regards.
       
      Wei