Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
StatisticsNerd
Regular Visitor

How can I slice multiple combined measures by a categorical group slicer?

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.

2 ACCEPTED SOLUTIONS
v-cgao-msft
Community Support
Community Support

Hi @StatisticsNerd ,

 

Is your original data similar to this, with all vendor information in the field names.

vcgaomsft_0-1662084142237.png

You can unpivot these fields in PowerQuery Editor.

vcgaomsft_1-1662084229946.png

Add a conditional column.

vcgaomsft_2-1662084429729.png

The [Vendor] field is then available for the slicer.

vcgaomsft_3-1662084496929.png

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

View solution in original post

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.

View solution in original post

6 REPLIES 6
v-cgao-msft
Community Support
Community Support

Hi @StatisticsNerd ,

 

Is your original data similar to this, with all vendor information in the field names.

vcgaomsft_0-1662084142237.png

You can unpivot these fields in PowerQuery Editor.

vcgaomsft_1-1662084229946.png

Add a conditional column.

vcgaomsft_2-1662084429729.png

The [Vendor] field is then available for the slicer.

vcgaomsft_3-1662084496929.png

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:

 

StatisticsNerd_0-1663083633658.png

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. 

StatisticsNerd_1-1663084649892.png

 

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:

StatisticsNerd_3-1663085282128.png

 

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.

parry2k
Super User
Super User

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.