cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ssicefox Frequent Visitor
Frequent Visitor

Display ratio in row

Hi,

 

I want to display ratio in row. It likes the image following.

Capture.JPG

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. 

 

Ratio.PNG

Could you help me?

I am looking forward to hearing from you soon.

 

Best regards.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ssicefox Frequent Visitor
Frequent Visitor

Re: Display ratio in row

Hi @Ashish_Mathur 

 

I realized my expected result following:

 

Ratio.PNG

 

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]
)
7 REPLIES 7
Super User
Super User

Re: Display ratio in row

Hi,

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

v-xicai Super Contributor
Super Contributor

Re: Display ratio in row

Hi @ssicefox ,

 

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

 

1.png

Best Regards,

Amy

 

ssicefox Frequent Visitor
Frequent Visitor

Re: Display ratio in row

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:
 
Capture - Copy.JPG

Could you help me?

I am looking forward to hearing from you soon.

 

Besta regards./

ssicefox Frequent Visitor
Frequent Visitor

Re: Display ratio in row

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

Capture.PNG
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 Super Contributor
Super Contributor

Re: Display ratio in row

Hi @ssicefox ,

 

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

 

ssicefox Frequent Visitor
Frequent Visitor

Re: Display ratio in row

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.
 
test.PNGtest.PNG
 
Best regards.
 
Wei
ssicefox Frequent Visitor
Frequent Visitor

Re: Display ratio in row

Hi @Ashish_Mathur 

 

I realized my expected result following:

 

Ratio.PNG

 

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]
)