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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

stacked and clustered column (basics)

Hello experts,

I am working on an indicator that has been produced based out of Excel. I had been asked to reproduce it in Power BI.

As per my collected info it seems clustered and stacked are not possible with PowerBI... Then I would appreciate to let me know if any "workaround to obtain the graph "Product Mix  as I am capable to obtain the accompanied table... In excel I would only nee to transpose values...

I achieve to create below chart based on calculated measures fields (thats why the % Sales need to be changed to % of total..not sure if correct...) Basically it shows contribution of top sellers in the portfolio and need to reduce long tail (slow movers)...

made chart.JPGexcel graph.JPG

 

thank you all in advance

 

BR

Luis

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

As you refer in Power BI when the values are too smal they aren't visible, you can make a custom formatting for each of the lines and column data lables, just turn on the customize series, however small values are not visible, a possible workaround is to:

 

  • Turn off data labels
  • Create 6 cards and make use of filters to choose the Sales or Number of Ref and each of the 3 measures
  • Format the cards without any background and small size letter
  • Place it on top of the bars

Untitled.png

Inconvinient on this is that if the size of the bars changes the values will remain on the same place so probably you will get value away from the bars.

 

Another way is to create a table and place it on bottom of the chart.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

21 REPLIES 21
MFelix
Super User
Super User

Hi  @Anonymous ,

 

I don't really understand what you mean by not being abble to achieve a cluster and stacked chart in PBI. You can use some work arounds to being abble to achieve it however that depends on your data and the setup.

 

Can you share some example of your data and expected outcome?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hola Miguel Felix

thanks for your support. I think this is a beginners problem... 😛

The expected outcome is the graph I put on my initial message.. I need to arrive to show those 2 columns as I do in excel: 1st column shows the % of references within each classification (A) Sum(#Top 30)/total #Ref, B)sum(#rest A)/Total#Ref & C) Sum(#All others)/total#Ref)  and 2nd column also the % of sales within same classification (A) Sum( sales(top30/Total Month Sales, B) sum(sales(rest A)/Total Month Sales & C) Sum(sales(All Others)/Total Month Sales.

My filters are set already for a specific month.

an example of my data currently loaded is:

excel Dso.JPG

 

and the wrongly graph produced in Power Bi is:


wrong.JPG

 

thanks again in advance

 

Hi @Anonymous 

 

Your information is calculated based on total values correct? In your example you only have values for the top30 are you abble to give some sample with the full data for all levels?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

hello @MFelix @v-diye-msft 

I tried to upload the excel here but I dont find an option here how to do it ...

clueless on how to forward you the pbx file i created 

thanks for letting me know.... 

Hi @Anonymous m,

 

You need to upload a onedrive, google drive or wetransfer type of web url to share files.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable


Sorry guys 

here below the excel file (modified for security) I used for calculating above measures and graphs.

https://drive.google.com/file/d/1UANLagy02o3VUlXaQmW45kX8yt3WDa3G/view?usp=sharing

thanks to let me know if the shared file works...

 

Anonymous
Not applicable

hi again

I changed the rights so everone can edit it.. hope is ok now...

https://drive.google.com/file/d/1UANLagy02o3VUlXaQmW45kX8yt3WDa3G/view?usp=sharing

 

Hi @Anonymous ,

 

It's asking for a password. can you share public


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hello @MFelix 

I made it public (editable).. let me know if OK now....

Hi @Anonymous ,

 

I created a table for the separation of the calculations with the following data:

 

Calculation ID
Number of ref. 1
Sales 2

 

Then I added the following 3 Measures:

TOP 30 = 
VAR top30_sales =
    CALCULATE (
        SUM ( 'Top Detail'[Annual Sales] );
        'Top Detail'[Market Rank] = "TOP 30"
    )
VAR top30_reference =
    CALCULATE (
        DISTINCTCOUNT ( 'Top Detail'[material reference] );
        'Top Detail'[Market Rank] = "TOP 30"
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( Split[ID] );
        1; (
            top30_reference
                / CALCULATE ( DISTINCTCOUNT ( 'Top Detail'[material reference] ) )
        );
        ( top30_sales / CALCULATE ( SUM ( 'Top Detail'[Annual Sales] ) ) )
    )

REST A = 
VAR restA_sales =
    CALCULATE (
        SUM ( 'Top Detail'[Annual Sales] );
        'Top Detail'[Market Rank] = "Rest A"
    )
VAR restA_reference =
    CALCULATE (
        DISTINCTCOUNT ( 'Top Detail'[material reference] );
        'Top Detail'[Market Rank] = "Rest A"
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( Split[ID] );
        1; (
            restA_reference
                / CALCULATE ( DISTINCTCOUNT ( 'Top Detail'[material reference] ) )
        );
        ( restA_sales / CALCULATE ( SUM ( 'Top Detail'[Annual Sales] ) ) )
    )

ALL OTHERS = 
VAR allothers_sales =
    CALCULATE (
        SUM ( 'Top Detail'[Annual Sales] );
        'Top Detail'[Market Rank] = "All Others"
    )
VAR allothers_reference =
    CALCULATE (
        DISTINCTCOUNT ( 'Top Detail'[material reference] );
        'Top Detail'[Market Rank] = "All Others"
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( Split[ID] );
        1; (
            allothers_reference
                / CALCULATE ( DISTINCTCOUNT ( 'Top Detail'[material reference] ) )
        );
        ( allothers_sales / CALCULATE ( SUM ( 'Top Detail'[Annual Sales] ) ) )
    )

 

Now just setup your data.

 

Please see attach file with full details.

 

In the file there is also line calculations but not sure if you want, they need to be done based on the sorting of the measures order you place on the columns.

 

Also please check if the calculations are correct based on the columns otherwise just change the columns.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Thanks Miguel

but can not open the PBIX file.. I got an error message: 

 

"Something went wrong.

PObject reference not set to an instance of an object..."<

 

Anyway if I understood well you created 3 measures (one per each type of ranked group) and within each measure I can see 2 variables (??? not yet there in my knowledge ) one for the count of references and the other for the amount of sum of sales per each group.... and another table linked to my main table ?

 

perhaps you can guide me what I need to read to understand your functions ?or give me a quick comments on your code?

 

thanks again

 

Hi @Anonymous ,

 

The two table are not related.

 

TOP 30 = 
VAR top30_sales =
    CALCULATE (
        SUM ( 'Top Detail'[Annual Sales] );
        'Top Detail'[Market Rank] = "TOP 30"
    ) // Variable to calculate TOP 30 Sales
VAR top30_reference =
    CALCULATE (
        DISTINCTCOUNT ( 'Top Detail'[material reference] );
        'Top Detail'[Market Rank] = "TOP 30"
    ) // Variable to calculate TOP 30 number of references
RETURN
    SWITCH (
        SELECTEDVALUE ( Split[ID] );
        1; (
            top30_reference
                / CALCULATE ( DISTINCTCOUNT ( 'Top Detail'[material reference] ) ) // If value on the split table is Number of ref. make division of reference variable by the number of references
        );
        ( top30_sales / CALCULATE ( SUM ( 'Top Detail'[Annual Sales] ) ) ) // If value on the split table is Sales make division of reference variable by the total sales
    )

 

Check comments above.

Then create your chart with the following setup:

  • Type of chart: Line and Stacked column chart
  • Shared Axis: Calculation (from the table we create)
  • Column Values: Top30 / Rest A  / All Others (Measures)

Be aware that you must format the measures as percentages.

 

Is the result below correct?

 

line_chart.pngAny addtional question please tell me.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Thanks a lot @MFelix ,

I think I have learned with you and your codes more than a expected to learn in a week ... 😜

I arrived to show the graph but only two things:

a. the columns are in inverted order (first appears the sales and then the proportion of product types)

b. if I use the distinctcount() function I got wrong percentages (sums to more than 100% ????) but if I change to count() I got correct numbers (see below graph).. Bizarre as even if i have repetitive lines should be less no more...

 

wrong.JPG

 

c. I still can not figure out how to have the lines linking the columns as per your screenshot... 

 

thanks again

 

 

Hi @Anonymous ,

 

Answering to your questions:

 

a) Click on the 3 dots on the right part of the chart and select the asdencing /descending order of the column you need to sort

 

b) I made the calculation based on the number of material references if you select any of the other columns the result will be different. When you are using the market rank your value is of 1 in all of the values because based no the context there is a single value.

This calculation needs to be done at the lowest level of granularity you have for this number of references, maybe needs to be changed to a countrows or similar.

 

c) The lines were made in a different way as I refer in the first post where I publish the file.

Be aware that this needs to be made taking into account the order you have your measure in the bar chart create the following 3 measures:

All others Line = [ALL OTHERS]

REST A Line = [ALL OTHERS] + [REST A]

TOP 30 Line = [ALL OTHERS] + [REST A] + [TOP 30]

Basically I'm doing cumulative lines on the other 3 measures, now just place this measures on the line part of the chart.

 

Any other questions please tell me.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

I am getting there ....

 

replying to the 3 points above:

a.  done… Now I need to order Top 30, Rest A and All others in that order.(I have read in the posts that I need to create a related table to order in that sequence...) 

Capture.JPG

b. Yes it is done at lowest level. To be clear the (DISTINCTCOUNT('Top Detail'[Desc.])) is the same field as your DISTINCTCOUNT ( 'Top Detail'[material reference] ). I just gave you a file with understandle titles.
As I said above I just used a measure Nbr of ref. with function count() and on the measure test 30 function “distinctcount() “ and as you can see not same results…

 

excel Dso.JPG

 

c. c) yes sorry. as I said your pbix file gives me an error when trying to open it … 

 

After this, I will be ready to move on ... I hope 🙂

Hi @Anonymous ,

 

a) The measures need to be place based on the order you want so if you want the to have first the TOP 30 that is the measure you need to place first on your chart then Rest A and All Others.

 

bar_lines.png

Be aware that if you are changing the orders then you also need to change the line measures to the following:

All others Line = [ALL OTHERS] + [REST A] + [TOP 30]

REST A Line = [TOP 30] + [REST A]

TOP 30 Line = [TOP 30]

 

b) Count and Distinctcount have different result because they are calculating different things.

    COUNT - counts the number of cells in a column that contain non-blank values

    DISTINCTCOUNT - Counts the number of distinct values in a column.

 

Basically one is counting all the values that are presented in the TOP 30 - 978 and the other the distinct references within top 30, making an example:

 

ABC
ABC
ABC
CCC
DDD

 

On the reference above the results for each of the formulas is:

 

  COUNT DISTINCTCOUNT
ABC 3 1
CCC 2 1
DDD 1 1
Total 5 3

 

I understood from your post that you wanted to make the count of the references so used distinctcount.

 

If you want the measure to calculate over the full number of rows in your dataset change it to COUNT.

 

Hope this helps to understand what I have done.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@MFelix, very thankful for all your support on my initial steps with Power Bi.

I finally achieved to obtain the graph i need as per your example.

as a final question for Power BI experts like you is how to show the small percentages on the Data Labels that I will always have for Top 30 or Rest A ? they are not visible on the sheet anymore. I read there is no solution for that in Power BI? (as not possible to choose "outside end"  )

In Excel I could show them Outside and even play with the font side of each one...

 If the reports are going to be revised by management not showing any value is a no-go....

thanks again... & again 🙂


Capture.JPG

 

Hi @Anonymous ,

 

As you refer in Power BI when the values are too smal they aren't visible, you can make a custom formatting for each of the lines and column data lables, just turn on the customize series, however small values are not visible, a possible workaround is to:

 

  • Turn off data labels
  • Create 6 cards and make use of filters to choose the Sales or Number of Ref and each of the 3 measures
  • Format the cards without any background and small size letter
  • Place it on top of the bars

Untitled.png

Inconvinient on this is that if the size of the bars changes the values will remain on the same place so probably you will get value away from the bars.

 

Another way is to create a table and place it on bottom of the chart.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @Anonymous ,

 

Don't forget to mark the correct answer for the post in order to help others.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

just to add:

I created a simple measure using distinctcount() and have totally different figures:

test 30 = CALCULATE(DISTINCTCOUNT('Top Detail'[Desc.])) ==> I got totally different numbers as Nbr of Ref. field (which is corrrect)

excel graph.JPG

 

Using market rank field in turn i got only 1s:

test 30 = CALCULATE(DISTINCTCOUNT('Top Detail'[market rank])) ==> Test 30 is all 1's
 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.