Total % of a column relative to the count (Dynamic) (Interactive) (Treemap or Bar Chart)

[Sebaspot]

Hello, 

I need some help to understand if i will be able to achive what i need based on the example here below: 
I have a large pool of data that are all in relationship with the "ID" column.

Here below the example i've created.

What i need to achive is a treemap that gives me in % the total of each country relative to the total count of ID. (Like this below)

Today i can make measures to accomplish it but they are only values and so the widget is not interactive with all the others, it's simply static. 

Perhaps i can accept a bar chart like the one, i can make in excel like this below: 

But as well when i make measures in Power Bi they are values and so i have no axis that can keep the widget interactive with the others.

Is there a way to solve my issue? 

Thank you.

[amitchandak]

@Sebaspot , First unpivot your data and then try a measure like this

https://radacad.com/pivot-and-unpivot-with-power-bi

measure = divide(Sum(Table[Value]),count(Table[value])) // Value is name of column after unpivot 

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

Appreciate your Kudos.

[Sebaspot]

@amitchandak 

Thank you for your reply. 

I've tried to unpivot all the other columns keeping "ID" as suggested by @nandukrishnavs but I get an error saying that the column "ID" can't have multiple same values.

If I try to unpivot all of them I get an awkward result.

I've uploaded both excel (source) and pbix file on one drive and here i share the link.
https://1drv.ms/u/s!AtfYRDzNj-lCh1uPAHkNZo84kVfz?e=7poJUz 

If you have a chance to take a look i've made a new sheet called "New" which i used to modify as you said but with no success. 

[nandukrishnavs · Accepted solution]

@Sebaspot 

Delete all the existing relationships.

Goto Advanced Editor, Copy below PQ in Original2 Table

let
    Source = Excel.Workbook(File.Contents("C:\Users\trave\Desktop\Trial.xlsx"), null, true),
    #"2_Sheet" = Source{[Item="2",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"2_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Italy", Int64.Type}, {"Germany", Int64.Type}, {"France", Int64.Type}, {"Spain", Int64.Type}, {"UK", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Germany", "Germany-"}, {"France", "France-"}, {"Italy", "Italy-"}, {"Spain", "Spain-"}, {"UK", "UK-"}, {"ID", "NewID"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"NewID"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = 1))
in
    #"Filtered Rows"

now enable the relationship

Create new DAX measure

Measure = divide(COUNTROWS(Original2),COUNTROWS(ALL(Original2))) 

Use this measure in the bottom Tree visual's value filed.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Sebaspot]

@nandukrishnavs 

Thank you very much for your help. 

I've read the code of the advance editor and understood that basically it was the relationship that was making the trouble. 

So by your suggestion i understood how to accomplish it and get the treemap well connected. 

The only issue i got now is that values are now totals and not % of total relative to the count of ID so basically i just need to turn the result in the correct value %. 

I've tried to understand the calculation but i guess i will find it out with time.

Do you have any suggestion? 

Thank you 

[nandukrishnavs]

@Sebaspot 

Did you use this measure, I have updated in my previous post.

Measure = divide(COUNTROWS(Original2),COUNTROWS(ALL(Original2)))

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂 

[Sebaspot]

Great!

I did it but i was missing "ALL" in the formula. 

Thank you very much for all the help! 

[nandukrishnavs]

@Sebaspot 

open Edit Query and select ID column, then right-click and select unpivot other columns. Then you will get Attributes and values. 

You can use these columns in visuals. Try this and share your comments 

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Sebaspot]

@nandukrishnavs
Thank you for your reply. 

I've tried to do that but i get an error saying that the column "ID" can't have multiple same values.

I've uploaded both excel (source) and pbix file on one drive and here i share the link.
https://1drv.ms/u/s!AtfYRDzNj-lCh1uPAHkNZo84kVfz?e=7poJUz 

If you have a chance to take a look i've made a new sheet called "New" which i used to modify as you said but with no success.