How to calculate the percentage using a constant value

[Anonymous]

Hello all,

I would want to display top 10 values while retaining the percentage to be calculated over 365 and not over the total of top 10. Hope I am able to convey my query and can get a resolution from this community.

[Anonymous · Accepted solution]

Hi @Anonymous ,

If you want to show Total by all table when you using TopN function in your visual, you can try HASONEVALUE function and IF to create a measure.

My Sample: 

Measures:

M_Value = 
IF (
    HASONEVALUE ( 'Table'[Origin] ),
    CALCULATE ( SUM ( 'Table'[Value] ) ),
    CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) )
)

Percentage% = 
VAR _VALUE =
    CALCULATE ( SUM ( 'Table'[Value] ) )
VAR _TOTAL =
    CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) )
RETURN
    IF ( HASONEVALUE ( 'Table'[Origin] ), DIVIDE ( _VALUE, _TOTAL ), 1 )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Anonymous 

Thankyou for your solution.

I tried your approach. But , below is my output.

I modified the formula as below:

Percentage% =

VAR _VALUE =

CALCULATE ( COUNT('data'[passengerid]), 'data'[Arr/Conn/Dep] = "Arriving", 'data'[adhocplanned] = "ad-hoc" )

VAR _TOTAL =

CALCULATE (COUNT('data'[passengerid]), 'data'[Arr/Conn/Dep] = "Arriving", 'data'[adhocplanned] = "ad-hoc", ALLEXCEPT ( 'data','data'[Year-Month] ) )

RETURN

IF ( HASONEVALUE ( 'data'[origin airport] ), DIVIDE ( _VALUE, _TOTAL ), 1 )

[MFelix]

Hi @Anonymous ,

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.