Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
The office column displays the value from the 'SharePoint List' [Value] that belongs to the filtered month or year (the year and month can be filtered based on month and/or year '2019 January' or 'January' ....... '2015').
Slicer1 - value from filter1. Filter1 contains year and month.
Slicer2 - value from filter2. Filter2 contains year and month.
+/- - value after filtering results from filter1 and filter2. The old date value is always deducted from the newer date value.
Excel is like:
Pointer | Period | Office1 | Office2 | Office3 | Office4 |
SUM people | Slicer 1 (Month, Year) | 25 | 27 | - | 20 |
Slicer 2 (Month, Year) | 30 | 17 | - | 30 | |
+/- | -5 | 10 | - | -10 | |
SUM people2 | Slicer 1 (Month, Year) | 12 | - | 5 | 16 |
Slicer 2 (Month, Year) | 13 | - | 10 | 13 | |
+/- | -1 | - | -5 | 3 |
SharePoint List is like:
Year | Month | Office | Value |
2016 | January | Office1 | 10 |
2017 | February | Office1 | 17 |
2019 | December | Office2 | 7 |
2015 | February | Office2 | 15 |
2020 | July | Office3 | 30 |
2016 | January | Office3 | 25 |
Slicer1 is same like Slicer2
Year | Month |
2016 | January |
2017 | February |
2019 | December |
2015 | February |
2020 | July |
2016 | January |
Can you advise me how I can do that, please?
I have probably misspoken about what I want. I need to get a table similar to a table in Excel from a SharePoint List.
Hi @cleemas ,
You may combine all the Offices columns into a separate column for the Excel data source. Enter into Query Editor, click on columns Office1-Office4 at the same time, then choose "Unpivot Columns" option, you can rename this result column "Attribute" with "Office" . Don't forget to click the "Close & Apply" button.
Then you can create measures like DAX to get total value for per Pointer.
Total sum=CALCULATE (SUM (Table1[Value] ),FILTER (ALLSELECTED(Table1), Table1[Pointer]= MAX(Table1[Pointer])))
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Assuming you are able to create two slicers. By using two disconnected tables or one connected and one disconnected table.
You can try formulas like
slicer 1 value = calculate(sum(table[value]),filter(table,table[month-year] in ALLSELECTED('Slicer1'[Format Month])))
slicer 2 value = calculate(sum(table[value]),filter(table,table[month-year] in ALLSELECTED('Slicer2'[Format Month])))
OR
slicer 1 value = calculate(sum(table[value]),table[month-year] in ALLSELECTED('Slicer1'[Format Month]))
slicer 2 value = calculate(sum(table[value]),table[month-year] in ALLSELECTED('Slicer2'[Format Month]))
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
User | Count |
---|---|
103 | |
87 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |