Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
devinep5
Frequent Visitor

How can I make my calculated columns dynamic?

I put together the following table as an example of my data table:

 

YearGenderScore(1-4)RangePercentage
1M33 & Above50%
1F33 & Above50%
2M44 & Above20%
2F11 & Above100%
3M11 & Above100%
3F33 & Above50%
4M11 & Above100%
4F44 & Above20%
5M22 & Above60%
5F11 & Above100%

 

I have created two calculated columns (Range) & (Percentage) and have then added them to the graph below. Which is exactly how I want my data to be shown. (Percentage of people achieving a value/ above).

 

devinep5_0-1594764203161.png

 

However, when I select 'M' or 'F', or Year 1', etc, the data does not change accordingly. How would I go about making the percentages dynamic to the slicer selections?

 
 
1 ACCEPTED SOLUTION

Hi @devinep5 ,

Please check whether the below screen shot is what you want? If yes, you can follow the below steps to achieve it(You can get my sample pbix file for the full details):

1. Create a dimension table with range column

2. Create a measure as below to get the percentage of per range

Percentage = 
VAR _a =
    CALCULATE (
        COUNT ( 'PT_1819'[Score(1-4)] ),
        FILTER (
            'PT_1819',
            'PT_1819'[Score(1-4)] >= VALUE ( LEFT ( MAX ( 'Range'[Range] ), 1 ) )
        )
    )
VAR _b =
    CALCULATE ( COUNT ( PT_1819[Score(1-4)] ), ALL ( PT_1819 ) )
RETURN
    DIVIDE ( _a, _b )

Expected result?Expected result?

If the above one is not what you want, please provide the calculation logic of calculated column Percentage and make example to explain your expected result when select the different Year and Gender. Thank you.

Best Regards

Rena

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

View solution in original post

7 REPLIES 7
v-yiruan-msft
Community Support
Community Support

Hi @devinep5 ,

Whether both Range and Percentage are calculated column? Could you please share the formulas of these two columns and the calculation logic? Later we may need to create Percentage as measure and update the related formulas in order to make it dynamically based on the selection of slicers. Thank you.

Best Regards

Rena

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

Hi, I used the following calculations:
1) Range:
Range =
SWITCH (
TRUE(),
PT_1819[Score (1-4)] >= 4, "4 & Above",
PT_1819[Score (1-4)] >= 3, "3 & Above",
PT_1819[Score (1-4)] >= 2, "2 & Above",
PT_1819[Score (1-4)] >= 1, "1 & Above",
Blank()
)
 
2) Percentage:
Column =
VAR _count =
COUNT ( PT_1819[Score (1-4)] )
VAR _C1 =
COUNTX (
FILTER ( 'PT_1819', 'PT_1819'[Score (1-4)] >= 1 ),
[Score (1-4)]
)
VAR _C2 =
COUNTX (
FILTER ( 'PT_1819', 'PT_1819'[Score (1-4)] >= 2 ),
[Score (1-4)]
)
VAR _C3 =
COUNTX (
FILTER ( 'PT_1819', 'PT_1819'[Score (1-4)] >= 3 ),
[Score (1-4)]
)
VAR _C4 =
COUNTX (
FILTER ( 'PT_1819', 'PT_1819'[Score (1-4)] >= 4 ),
[Score (1-4)]
)
RETURN
SWITCH (
TRUE (),
'PT_1819'[Score (1-4)] >= 4, _C4 / _count,
'PT_1819'[Score (1-4)] >= 3, _C3 / _count,
'PT_1819'[Score (1-4)] >= 2, _C2 / _count,
'PT_1819'[Score (1-4)] >= 1, _C1 / _count,
BLANK ()
)

Hi @devinep5 ,

Please check whether the below screen shot is what you want? If yes, you can follow the below steps to achieve it(You can get my sample pbix file for the full details):

1. Create a dimension table with range column

2. Create a measure as below to get the percentage of per range

Percentage = 
VAR _a =
    CALCULATE (
        COUNT ( 'PT_1819'[Score(1-4)] ),
        FILTER (
            'PT_1819',
            'PT_1819'[Score(1-4)] >= VALUE ( LEFT ( MAX ( 'Range'[Range] ), 1 ) )
        )
    )
VAR _b =
    CALCULATE ( COUNT ( PT_1819[Score(1-4)] ), ALL ( PT_1819 ) )
RETURN
    DIVIDE ( _a, _b )

Expected result?Expected result?

If the above one is not what you want, please provide the calculation logic of calculated column Percentage and make example to explain your expected result when select the different Year and Gender. Thank you.

Best Regards

Rena

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

Hi,

I can try to help.  Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

How have you calculated the 2 columns?  Share the business logic and also the formulas.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
mahoneypat
Employee
Employee

You can keep your range column as is, but make a dynamic measure like this (replace Table with your table name):

 

 

Pct At or Above =
VAR __thisscore =
    MIN ( Table[Score] )
VAR __total =
    CALCULATE ( COUNTROWS ( Table ), ALLSELECTED ( Table ) )
VAR __atorabove =
    CALCULATE ( COUNTROWS ( Table ), ALLSELECTED ( Table ), Table[Score] >= __thisscore )
RETURN
    DIVIDE ( __atorabove, __total )

 

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.