Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have a report in which I show 4 distinct bar charts sliceable by month or week. The bar charts themselves combine information from distinct colums to create cumulative summed measures. For example purposes, I have summed various columns for each line for each part.
Actual Part 1 = sum('sheet (1)'[actual vendor1 part1]) + sum('sheet (1)'[actual vendor2 part1])
Plan Part 1 = sum('sheet (1)'[plan vendor1 part1]) + sum('sheet (1)'[plan vendor2 part1])
Plan to Actual Delta Part 1 = sum('sheet (1)'[plan to delta vendor1 part1])+ sum('sheet (1)'[plan to delta vendor2 part1])
The same follows for the other three respective parts shown on the report.
What I am hoping to do is create a slicer that can display a group of measures contained in the above calculated measures by Vendor.
Essentially if Vendor1 is selected in a slicer, I'm hoping that only the data correlating to vendor1 will show in the line charts, and visa versa, Vendor2.
I'm not sure If I can create a table for each vendor and link these different measures (12 to each vendor for the overall report) to a single designator slicer option, or if that is currently impossible with DAX.
Any advice would be greatly appreciated.
Solved! Go to Solution.
Hi @StatisticsNerd ,
Is your original data similar to this, with all vendor information in the field names.
You can unpivot these fields in PowerQuery Editor.
Add a conditional column.
The [Vendor] field is then available for the slicer.
Some modifications to the original measures are needed.
For example.
Actual Part 1 =
CALCULATE (
SUM ( 'sheet (1)'[Value] ),
FILTER (
'sheet (1)',
'sheet (1)'[Attribute] IN { "actual vendor1 part1", "actual vendor2 part1" }
)
)
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
I think this may be the way to solve my issue, and seems much more straight forward than attempting to make multiple dax calculations to unsort the data. I will need to give it a test. I may have to figure out how to continue to use the measures as a line as rows as oppossed to columns.
Thank you for your effort. Of course if it solves the issue I will mark it as the solution.
Hi @StatisticsNerd ,
Is your original data similar to this, with all vendor information in the field names.
You can unpivot these fields in PowerQuery Editor.
Add a conditional column.
The [Vendor] field is then available for the slicer.
Some modifications to the original measures are needed.
For example.
Actual Part 1 =
CALCULATE (
SUM ( 'sheet (1)'[Value] ),
FILTER (
'sheet (1)',
'sheet (1)'[Attribute] IN { "actual vendor1 part1", "actual vendor2 part1" }
)
)
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
I couldn't get this to work as it creates a single value line rather than a cumulative display as desired below.
Prior to doing the pivot, here is the desired report presentation:
Here is the way I was able to create a combined measure combining different columns prior to attempting the pivot for the above desired look:
Actual 120-0935 =
SUM('Sheet1 (2)'[Actual Ozdisan 120-0935]) + SUM('Sheet1 (2)'[Actual TSI 120-0935])
Actual 120-0936 =
SUM('Sheet1 (2)'[Actual Osdizan 120-0936]) + SUM('Sheet1 (2)'[Actual TSI 120-0936])
Actual 120-0948 =
SUM('Sheet1 (2)'[Actual Ozdisan 120-0948]) + SUM('Sheet1 (2)'[Actual TSI 120-0948])
Actual 120-0953 =
SUM('Sheet1 (2)'[Actual Ozdisan 120-0953]) + SUM('Sheet1 (2)'[Actual TSI 120-0953])
Plan 120-0935 =
SUM('Sheet1 (2)'[Plan Ozdisan 120-0935]) + SUM('Sheet1 (2)'[Plan TSI 120-0935])
Plan 120-0936 =
SUM('Sheet1 (2)'[Planned TSI 120-0936]) + SUM('Sheet1 (2)'[Plan Ozdizan 120-0936])
Plan 120-0948 =
SUM('Sheet1 (2)'[Plan Ozdisan 120-0948]) + SUM('Sheet1 (2)'[Plan TSI 120-0948])
Plan 120-0953 =
SUM('Sheet1 (2)'[Plan TSI 120-0953]) + SUM('Sheet1 (2)'[Plan Ozdisan 120-0953])
Plan to Actual Delta 120-0935 =
SUM('Sheet1 (2)'[Plan to Actual Delta Ozdizan 120-0935]) + SUM('Sheet1 (2)'[Plan to Actual Delta TSI 120-0935])
Plan to actual Delta 120-0936 =
SUM('Sheet1 (2)'[Plan to Actual Delta Ozdisan 120-0936]) + SUM('Sheet1 (2)'[Plan to actual Delta TSI 120-0936])
Plan to Actual Delta 120-0948 =
SUM('Sheet1 (2)'[Plan to Actual Delta Ozdisan 120-0948]) + SUM('Sheet1 (2)'[Plan to Actual Delta TSI 120-0948])
Plan to Actual Delta 120-0953 =
SUM('Sheet1 (2)'[Plan to Actual Delta TSI 120-0953]) + SUM('Sheet1 (2)'[Plan to Actual Delta Ozdisan 120-0953])
Your previous tip to create a pivot table did work out to utilizing conditional coloumns did allow me too assign these multiple columns into the two desired categorical categories; however, as I am also tracking time, the above dax didn't allow me to calculate similar measures which display graphically as those above.
This may be because your example calculation didn't contain the date column information?
However, rather than showing a cumulative graph as desired above it displays the following:
I can provide the data file and report if it will be beneficial; however, I'm unsure how to do that here.
Hi @StatisticsNerd ,
Please refer:
How to provide sample data in the Power BI Forum
Please delete sensitive information from the file. If this is not relevant to the topic of the thread, we suggest you create a new one, thanks in advance!
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Thank you Gao,
After a few small tweeks to the Dax you provided I was able to get the report to display as desired, and to work with the categorical slicer.
Marked above as the solution.
Thank you so much for your help, and the above resource. It will be very useful If I post another question to the forum in the future.
Best regards,
-StatisticsNerd
I think this may be the way to solve my issue, and seems much more straight forward than attempting to make multiple dax calculations to unsort the data. I will need to give it a test. I may have to figure out how to continue to use the measures as a line as rows as oppossed to columns.
Thank you for your effort. Of course if it solves the issue I will mark it as the solution.
@StatisticsNerd Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
89 | |
83 | |
76 | |
64 |
User | Count |
---|---|
142 | |
112 | |
98 | |
98 | |
94 |