Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Lets see if I can frame this and if anyone has idea to resolve it:
So I have a requirement where I have got the below excel from one of the user. The graph you see depends on the Month value selected in one the row(in pink). On selecting the month row I get the graph plotted accordingly. See the two example below( for month June and October). Based on month selection the value in YTD. You can see the value of YTD2 and FC2 and YTD+FC column value changes based on month selection.
The same functionality I am trying to achieve in POWER BI but I dont see a visuals. By the help of this forum I was able to create the tabular data as below( I hope i have done right calculation) but now stuck at showing it in visuals. Please provide your expertise on resolving this. I am attaching the excel file in case more clarification is required.
I dont see an option to attach excel file 🙂
Solved! Go to Solution.
Hi @Anonymous,
I found a solution finally. Please refer to the following steps. You can try it out in this file.
1. Create a new table "Date" that has all the dates from old table. Don't establish relationship.
2. Create three new measures.
Initial forcast = sum([Initial FC **bleep**])
Series 2 = IF ( MIN ( 'Table1'[Date] ) <= MIN ( 'Date'[Date] ), 0, CALCULATE ( SUM ( Table1[YTD] ), FILTER ( ALL ( 'Table1' ), 'Table1'[Date] <= MIN ( 'Date'[Date] ) ) ) + CALCULATE ( SUM ( Table1[Forcast] ), FILTER ( ALL ( 'Table1' ), 'Table1'[Date] > MIN ( 'Date'[Date] ) && 'Table1'[Date] <= MIN ( 'Table1'[Date] ) ) ) )
YTD+FC Measure = IF ( MIN ( 'Table1'[Date] ) <= MIN ( 'Date'[Date] ), CALCULATE ( SUM ( Table1[YTD] ), FILTER ( ALL ( 'Table1' ), 'Table1'[Date] <= MIN ( 'Table1'[Date] ) ) ), CALCULATE ( SUM ( Table1[YTD] ), FILTER ( ALL ( 'Table1' ), 'Table1'[Date] <= MIN ( 'Date'[Date] ) ) ) + CALCULATE ( SUM ( Table1[Forcast] ), FILTER ( ALL ( 'Table1' ), 'Table1'[Date] <= MIN ( 'Table1'[Date] ) && 'Table1'[Date] > MIN ( 'Date'[Date] ) ) ) )
3. Create a slicer whose field is from Date table.
4. Create a Line chart.
Best Regards,
Dale
Hi @Anonymous,
You can upload the file to a cloud drive like OneDrive, Dropbox, and then share the link here that I can download from. The visuals could be Line chart and Slicer. Which is the expected result? It seems the data in the Power BI is different from the data in the Excel.
Best Regards,
Dale
Hi Dale,
Thanks for being such a great help.
Here is the link to the drop box. The expected result is the one we see on Excel sheet that on changing month , we get different values for YTD2 and YTD +FC and accordingly the graph is plotted. Yes the data values in Power Bi is different, I just wanted to achieve what is there in Excel, and I hope I have done the calculation as it is in Excel sheet
Link to Excel Sheet
Look to hear soon from you
Hi @Anonymous,
I imported the table "Table66" directly and created a Line chart. Is the one below what you want? You can check it out in this file.
Best Regards,
Dale
Hi Dale,
Were you able to get anything on this? Can you suggest if its even possible in Power Bi or not. Thanks
Hi @Anonymous,
I found a solution finally. Please refer to the following steps. You can try it out in this file.
1. Create a new table "Date" that has all the dates from old table. Don't establish relationship.
2. Create three new measures.
Initial forcast = sum([Initial FC **bleep**])
Series 2 = IF ( MIN ( 'Table1'[Date] ) <= MIN ( 'Date'[Date] ), 0, CALCULATE ( SUM ( Table1[YTD] ), FILTER ( ALL ( 'Table1' ), 'Table1'[Date] <= MIN ( 'Date'[Date] ) ) ) + CALCULATE ( SUM ( Table1[Forcast] ), FILTER ( ALL ( 'Table1' ), 'Table1'[Date] > MIN ( 'Date'[Date] ) && 'Table1'[Date] <= MIN ( 'Table1'[Date] ) ) ) )
YTD+FC Measure = IF ( MIN ( 'Table1'[Date] ) <= MIN ( 'Date'[Date] ), CALCULATE ( SUM ( Table1[YTD] ), FILTER ( ALL ( 'Table1' ), 'Table1'[Date] <= MIN ( 'Table1'[Date] ) ) ), CALCULATE ( SUM ( Table1[YTD] ), FILTER ( ALL ( 'Table1' ), 'Table1'[Date] <= MIN ( 'Date'[Date] ) ) ) + CALCULATE ( SUM ( Table1[Forcast] ), FILTER ( ALL ( 'Table1' ), 'Table1'[Date] <= MIN ( 'Table1'[Date] ) && 'Table1'[Date] > MIN ( 'Date'[Date] ) ) ) )
3. Create a slicer whose field is from Date table.
4. Create a Line chart.
Best Regards,
Dale
Hi Dale,
Were you able to get anything on this? Can you suggest if its even possible in Power Bi or not. Thanks
Yes this is what I am looking for. But if you see in Excel, I have option to change the month in one of the row highlighted in pink just above the graph. Based on the month my line graph changes along with values in YTD+FC and FC2. I would like to do the same calculation for that in Power Bi and same visualization.
So in short I have input as Month, Cumalative FC and Cumalative YTD. and have to plot and design as it is in Excel. Thanks.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |