cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
diana66
Frequent Visitor

Adding a slicer from different columns; calculating sum based on values in other columns

Hello,

I have the following example table containing data about some products:

 

Capture.PNG

 

 

 

I want to make a report and I struggle with some issues:

- I would like to have a slicer for Period 1 and Period 2, if I choose Period 1 I would like to see all the info from the Period 1 columns (last year, this year, share, change) and the same for Period 2.

- For the Share column, I should have the brand share, but depending on Market, Measure and Category. So, for ex. what is the share for tomatoes in the vegetables category, volum measure and hypermarket. I calculated this using the following DAX sintax, (period1/calculate(sum(period1; allexcept measure; category), but in order to have them in the report I have to create those columns with share in Edit Query, and I don't know how.  

Could you, please, help me?

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
vivran22 Solution Sage
Solution Sage

Re: Adding a slicer from different columns; calculating sum based on values in other columns

Hello @diana66 

 

For the first problem, I would recommend you to transform your table using Power Query:

 

Unpivot your value columns:

1.png

 

Split the Attribute column(earlier headers) to create two separate columns:

2.png

 

 

This way you should be able to filter your visuals using slicers:

 

3.PNG

 

 

For the % share, it is not very clear what is the output you are looking for. Is this the output you are looking for?

4.PNG

 

I have created following measures for this:

 

 

Total fruits =
CALCULATE (
    SUMX ( dtTable, IF ( dtTable[Category] = "fruits", dtTable[Value] ) ),
    ALLEXCEPT ( dtTable, dtTable[Category] )
)


Total Vegetables =
CALCULATE (
    SUMX ( dtTable, IF ( dtTable[Category] = "Vegetables", dtTable[Value] ) ),
    ALLEXCEPT ( dtTable, dtTable[Category] )
)


% Share fruits = DIVIDE(SUM(dtTable[Value]),[Total fruits])

% Share Vegetables = DIVIDE(SUM(dtTable[Value]),[Total Vegetables])

 

 

You may find the solution pbix file here

 

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/

View solution in original post

4 REPLIES 4
vivran22 Solution Sage
Solution Sage

Re: Adding a slicer from different columns; calculating sum based on values in other columns

Hello @diana66 

 

For the first problem, I would recommend you to transform your table using Power Query:

 

Unpivot your value columns:

1.png

 

Split the Attribute column(earlier headers) to create two separate columns:

2.png

 

 

This way you should be able to filter your visuals using slicers:

 

3.PNG

 

 

For the % share, it is not very clear what is the output you are looking for. Is this the output you are looking for?

4.PNG

 

I have created following measures for this:

 

 

Total fruits =
CALCULATE (
    SUMX ( dtTable, IF ( dtTable[Category] = "fruits", dtTable[Value] ) ),
    ALLEXCEPT ( dtTable, dtTable[Category] )
)


Total Vegetables =
CALCULATE (
    SUMX ( dtTable, IF ( dtTable[Category] = "Vegetables", dtTable[Value] ) ),
    ALLEXCEPT ( dtTable, dtTable[Category] )
)


% Share fruits = DIVIDE(SUM(dtTable[Value]),[Total fruits])

% Share Vegetables = DIVIDE(SUM(dtTable[Value]),[Total Vegetables])

 

 

You may find the solution pbix file here

 

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/

View solution in original post

diana66
Frequent Visitor

Re: Adding a slicer from different columns; calculating sum based on values in other columns

Hi @vivran22 

Thank you so much for your reply! The slicer solution worked perfectly.

For the share part, it works, but the problem is that I have a lot of categories and can't introduce them manually. Is there any way of calculating without adding the name of it? And I have to calculate the share for other indicators as well. Sorry, my first explanation was not the most understandable.

In the Period columns I have sales values. I have to have 3 types of analysis:
- one for brand level, splited by market. The category should have 100% in all market types, 100% in Sales and 100% in Volum.

p1.PNG
- one for supplier level, spilited by market. Also the category should have 100% in all market types, 100% in Sales and 100% in Volum.

p2.PNG


I have created a draft report, with how it should look like: (Private label is just another column where I have "Private label" and "No private label".). The example is for Brand level, I also have to have it for supplier level; and also with the slicer for Period 1 or Period 2. p5.PNGp4.PNG

 

p3.PNG

 

I hope I explained it right. 

 

Thank you so much again!

vivran22 Solution Sage
Solution Sage

Re: Adding a slicer from different columns; calculating sum based on values in other columns

Hey,

 

Still not very clear in terms of output required

 

Is this kind of calculation/value you are looking at?

 

5.PNG

pbix file

 

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/

diana66
Frequent Visitor

Re: Adding a slicer from different columns; calculating sum based on values in other columns

Hi @vivran22 ,

 

I think I figured it out. This is how it should look like: 

 

p6.PNGEach channel(hypermarket, supermarket, country, etc) should have 100%. I used unpivot columns for "Brand and Supplier" to be able to use it as slicer(in an Attribute column), like you advised me. I used the following formula for value % share:

Value % Share = 'Table'[Period 1 This year]/calculate(sum(Table[Period 1 This year]);ALLEXCEPT(Table;Table[MARKET];Table[Measure];Table[CATEGORY];Table[Attribute]))

 

I think I will make 2 reports, one for period 1 and one for period 2.

 

Thank you again!!

Diana

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors