Display ratio in row

[Anonymous]

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('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")))

I don't understand why the sublevel of "NET ACTIVITY TURNOVER" calculate ratio too. 

Could you help me?

I am looking forward to hearing from you soon.

Best regards.

[v-xicai]

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]

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]

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]

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

[Anonymous]

Hi, @v-xicai and @Ashish_Mathur 

Thank you for your reply. Yes, it's one of my questions. Why were the sublevels of NET ACTIVITY TURNOVER calculated?  I created a formula following to calculate Net margin / NET ACTIVITY TURNOVER

Ratio % = DIVIDE(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")))

I want to put ratio under the Net margin or Total, it likes this:

Could you help me?

I am looking forward to hearing from you soon.

Besta regards./

[Ashish_Mathur]

Hi,

I do not understand your requirement.  What is your expected result?

[Anonymous · Accepted solution]

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]

)