Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am trying to create a visualisation that will show movement of all share classes against a "Base share class". The user shall have an option to change the share class probably from a drop down and the dashboard shall update the calculation accordingly. Below is a table to help understand the scenario better.
There are 3 share classes at different NAV dates. For eg- If user want to select share class A as "Base share class" in that case NAV of share Class of B & C on different dates shall be divided by NAV of share class A and then plot the values on graph. Same way if user selects the share class B then the same calculation shall be repeated for different nav dates.
I have two questions for same
1. Is there anyway i can acheive the above calculation?
2. (This is a sepearate query from above) -Is there any feature in PBI which helps me in generating dynamic graphs on the basis of a variable factor. For eg:- In the below data there are 3 share classes , so 3 individual graphs can be created. If my data set has 4 share classes then in that case 4 graphs are generated.
share_partner_class | navdate_1 | navdate_2 | net_nav_share |
A | 01/03/2019 | 01/03/2019 | 2.150803356 |
A | 04/03/2019 | 04/03/2019 | 2.160175139 |
A | 05/03/2019 | 05/03/2019 | 2.15863532 |
B | 01/03/2019 | 01/03/2019 | 2.189851624 |
B | 04/03/2019 | 04/03/2019 | 2.19944201 |
B | 05/03/2019 | 05/03/2019 | 2.197900602 |
C | 01/03/2019 | 01/03/2019 | 0.952177298 |
C | 04/03/2019 | 04/03/2019 | 0.95635438 |
C | 05/03/2019 | 05/03/2019 | 0.955684664 |
Solved! Go to Solution.
Hi, @Sandeep_rai ,
Maybe you can use line chart , first create a slicer table and use it for slicer:
slicer = VALUES('Table'[Share class])
Then create the following measure for line chart value:
Measure =
IF (
HASONEVALUE ( slicer[Share class] ),
SWITCH (
SELECTEDVALUE ( slicer[Share class] ),
"A",
IF (
MAX ( 'Table'[Share class] ) = "A",
BLANK (),
MAX ( 'Table'[NAV] )
/ CALCULATE ( MAX ( 'Table'[NAV] ), 'Table'[Share class] = "A" )
),
"B",
IF (
MAX ( 'Table'[Share class] ) = "B",
BLANK (),
MAX ( 'Table'[NAV] )
/ CALCULATE ( MAX ( 'Table'[NAV] ), 'Table'[Share class] = "B" )
),
"C",
IF (
MAX ( 'Table'[Share class] ) = "C",
BLANK (),
MAX ( 'Table'[NAV] )
/ CALCULATE ( MAX ( 'Table'[NAV] ), 'Table'[Share class] = "C" )
)
),
MAX ( 'Table'[NAV] )
)
For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EbkrYCycLfxKqdOJ_O...
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
@Sandeep_rai - Small multiples are not quite here yet (soon). I believe this is what you are talking about. For now you will need to use something like a line chart with your share in the legend or manually create multiple charts and filter them to the individual share types.
@Sandeep_rai , The information you have provided is not making the problem clear to me. Can you please explain with an example.
You can try Sankey Visual
https://powerbi.microsoft.com/en-us/blog/visual-awesomeness-unlocked-sankey-diagram/
Appreciate your Kudos.
Thanks for replying amit. I will try to rephrase the question again.
I am trying to do a calculation in PBI which will eventually help me in creating the visualisation.
There are 3 share classes , A, B & C . NAV of each share class is calculated at regular interval ( for this example lets suppose daily). Now if i want to compare the performance of 1 share class against other share classes I will have to recalcuate the NAV of other 2 share classes. So in the below example- If i select "A" as a base share class in a drop down, then on each NAV date ie 01/03/2019, 04/03/2019 & 05/03/2019, NAV of Share class B & C shall be divided by share class A. Now as a user if i want to change my base share class to "B" same calculation shall happen automatically i.e NAV of share class A & C shall be divided by share class B on each NAV date and the graph shall be updated automatically once the calculation is done.
Is there anyway i can share my whole report as i don't see an attachement section here if this is not clear
Share class | Nav Date | NAV |
A | 01/03/2019 | 2.15080 |
A | 04/03/2019 | 2.16018 |
A | 05/03/2019 | 2.15864 |
B | 01/03/2019 | 2.18985 |
B | 04/03/2019 | 2.19944 |
B | 05/03/2019 | 2.19790 |
C | 01/03/2019 | 0.95218 |
C | 04/03/2019 | 0.95635 |
C | 05/03/2019 | 0.95568 |
Hi, @Sandeep_rai ,
Maybe you can use line chart , first create a slicer table and use it for slicer:
slicer = VALUES('Table'[Share class])
Then create the following measure for line chart value:
Measure =
IF (
HASONEVALUE ( slicer[Share class] ),
SWITCH (
SELECTEDVALUE ( slicer[Share class] ),
"A",
IF (
MAX ( 'Table'[Share class] ) = "A",
BLANK (),
MAX ( 'Table'[NAV] )
/ CALCULATE ( MAX ( 'Table'[NAV] ), 'Table'[Share class] = "A" )
),
"B",
IF (
MAX ( 'Table'[Share class] ) = "B",
BLANK (),
MAX ( 'Table'[NAV] )
/ CALCULATE ( MAX ( 'Table'[NAV] ), 'Table'[Share class] = "B" )
),
"C",
IF (
MAX ( 'Table'[Share class] ) = "C",
BLANK (),
MAX ( 'Table'[NAV] )
/ CALCULATE ( MAX ( 'Table'[NAV] ), 'Table'[Share class] = "C" )
)
),
MAX ( 'Table'[NAV] )
)
For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EbkrYCycLfxKqdOJ_O...
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |