Re Basing the value on the basis of dynamic selection of another value

[Sandeep_rai]

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

[Greg_Deckler]

@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.

[amitchandak]

@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.

[Sandeep_rai]

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

[v-deddai1-msft · Accepted 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