topic Re: Enter Total BAR in Bar Chart in DAX Commands and Tips

Enter Total BAR in Bar Chart

ashwani_gupta — Tue, 06 Feb 2024 09:50:26 GMT

Hello All,

I am facing problems in getting Total as one of the Bar in Bar Chart. My current Graph looks like this:

I want Total (i.e. 13970) also to come as a BAR in above BAR Chart.

My Tables structure is like this

Table 1: Computers (List of Computers and its Principal User (below is only Sample Data))

ID	Shared Device	Principal User	Principle State	Location
C1	0	A1	Active	Stuttgart
C2	0	A1	Active	Stuttgart
C3	0	A1	Active	Stuttgart
C4	0	A2	Active	Stuttgart
C5	0	A1	Active	Stuttgart
C6	0	A3	Active	Stuttgart
C7	0	A1	Active	Stuttgart
C8	0	A1	Active	Stuttgart
C9	0	A1	Active	Stuttgart
C10	0	A1	Active	Stuttgart
C11	0	A1	Active	Stuttgart
C12	0	A1	Active	Stuttgart
C13	0	A1	Active	Stuttgart
C14	0	A1	Active	Stuttgart
C15	0	A1	Active	Stuttgart
C16	0	A1	Active	Stuttgart
C17	0	A4	Active	Stuttgart
C18	0	A5	Active	Stuttgart

In table 1, I have created an additional column as

IndexCreated = 1

Table 2: Country (Mapping of Location and Country)

Location	Country
Itupeva	Brazil
Changchun	China
Changsha	China
Changzhou	China
Drancy	Portugal
Stuttgart	Germany
Stuttgart 2	Germany
Hildesheim	Germany
Global	Germany
Ingolstadt	Germany
Muenchen	Germany
Schwieberdingen	Germany
Miskolc	Hungary
Bengaluru	India
Torino	Portugal
Lerma	Mexico
Vila do Conde	Portugal
Treto	Spain
Yokohama	Portugal
Novi	Mexico
Yongin-Si	Portugal
Germany	Germany
Portugal	Portugal
Total	Total

What is my output graph: In my graph, I want to highlight whoever User is using more than 1 computer is basically having Saving Potential if we take additional computers back from these users. So, I am trying to count all users having more than 1 computers assigned to them. Sum all these additional computers in that country and subratct total number such "Users with multiple computers" in that country. The subtraction is to be performed to count only additional computers. For ex: if in a country there are 3 users with User1 having Comp1, Comp2, Comp3. User 2 having Comp4, Comp5 and User 3 having Comp6 assigned to them.

Additonal Comp with User1: Comp2 and Comp3

Addditional Comp with User2: Comp5

Additional Comp with User3: NA

Total Additional Asset in that country: 3 (Comp2, Comp3 and Comp5)

I have tried to create Measure like this in Table 2: Country (But it didn't work)

MPCS = VAR CurrentCountry = SELECTEDVALUE ( 'Country'[Country] ) var tab1= FILTER(SUMMARIZE(computers,computers[Principal User],"Total Count",SUM(computers[IndexCreated])),[Total Count]>1) var TAD = CALCULATE(SUM(computers[IndexCreated]),tab1) var AD= TAD - COUNTROWS(FILTER(SUMMARIZE(computers,computers[Principal User],"Total Count",SUM(computers[IndexCreated])),[Total Count]>1)) VAR CountTotal = SUMX ( ALLSELECTED ( 'Country'[Country] ), var tab2= FILTER(SUMMARIZE(computers,computers[Principal User],"Total Count",SUM(computers[IndexCreated])),[Total Count]>1) var TAD2 = CALCULATE(SUM(computers[IndexCreated]),tab2) var AD2= TAD2 - COUNTROWS(FILTER(SUMMARIZE(computers,computers[Principal User],"Total Count",SUM(computers[IndexCreated])),[Total Count]>1)) RETURN IF(AD2>0,AD2,0) ) VAR AdditionalCount = IF ( CurrentCountry = "Total", CountTotal, AD ) VAR AdditionalCost = AdditionalCount * 20 RETURN AdditionalCost

My understaning here is this part:

var tab2= FILTER(SUMMARIZE(computers,computers[Principal User],"Total Count",SUM(computers[IndexCreated])),[Total Count]>1)
var TAD2 = CALCULATE(SUM(computers[IndexCreated]),tab2)

is working correctly outside sumx, but inside sumx is not working. How can I make it work? Or any other alternate approach?

Re: Enter Total BAR in Bar Chart

some_bih — Tue, 06 Feb 2024 13:25:36 GMT

Hi ashwani_gupta it is not clear to me what "else" you are doing with your chart or what is your final request, like total is not correct or something else, like you want to know why is not/ working SUMX...

Re: Enter Total BAR in Bar Chart

ashwani_gupta — Wed, 07 Feb 2024 04:41:41 GMT

some_bih : I want to get a "Total Bar" in this chart:

The value of "Total Bar" is sum from all the countries. The meausre that I am trying is not working properly. The counties Bar value is coming correct but "Total Bar" is not showing.

Re: Enter Total BAR in Bar Chart

some_bih — Wed, 07 Feb 2024 09:09:03 GMT

Hi ashwani_gupta it is not totally possible to spot issue without model.

Still, try to replace

ALLSELECTED with VALUES

Otherwise share pbi file

Re: Enter Total BAR in Bar Chart

ashwani_gupta — Fri, 09 Feb 2024 11:58:26 GMT

some_bih : I am pasting here screenshot of my Relationship:

I tried changing ALLSELECTED with VALUES, but it didn't change anything. My Graph still looking like same:

Bar with "TOTAL" still not coming.

My measure (in the Table: Country) looks like this now:

MPCS = VAR CurrentCountry = SELECTEDVALUE ( 'Country'[Country] ) var tab1= FILTER(SUMMARIZE(computers,computers[Principal User],"Total Count",SUM(computers[IndexCreated])),[Total Count]>1) var TAD = CALCULATE(SUM(computers[IndexCreated]),tab1) var AD= TAD - COUNTROWS(FILTER(SUMMARIZE(computers,computers[Principal User],"Total Count",SUM(computers[IndexCreated])),[Total Count]>1)) VAR CountTotal = SUMX ( VALUES('Country'[Country] ), var tab2= FILTER(SUMMARIZE(computers,computers[Principal User],"Total Count",SUM(computers[IndexCreated])),[Total Count]>1) var TAD2 = CALCULATE(SUM(computers[IndexCreated]),tab2) var AD2= TAD2 - COUNTROWS(FILTER(SUMMARIZE(computers,computers[Principal User],"Total Count",SUM(computers[IndexCreated])),[Total Count]>1)) RETURN IF(AD2>0,AD2,0) ) VAR AdditionalCount = IF ( CurrentCountry = "Total", CountTotal, AD ) VAR AdditionalCost = AdditionalCount * 20 RETURN AdditionalCost

How can I share PBIX to you? Due to organization restriction, I can't upload in Onedrive or Google Drive to share with you the PBIX file.

Re: Enter Total BAR in Bar Chart

some_bih — Fri, 09 Feb 2024 13:02:47 GMT

Hi ashwani_gupta if you have Microsoft 365 account you can share link for file

Re: Enter Total BAR in Bar Chart

talespin — Mon, 12 Feb 2024 05:30:37 GMT

hi ashwani_gupta

Is this what you wanted?

Logic :

Count distinct users per location, country and subtract from count of computers per location, country.

Create a summarized table with all the countries and then calculate above information, then aggregate at desired level.

DimCountry includes a row for Total.

Measure

-------------------

Spare Computers =

VAR _SelCountry = SELECTEDVALUE(DimCountry[Country])

VAR _SummTbl =

ADDCOLUMNS(

SUMMARIZE(ALL(DimCountry[Country], DimCountry[Location]), DimCountry[Country], DimCountry[Location]),

"@UserCount",

VAR _Country = [Country]

VAR _Location = [Location]

RETURN CALCULATE(DISTINCTCOUNT(FactComputer[Principal User]), NOT(ISBLANK(FactComputer[Principal User])), DimCountry[Country] = _Country, DimCountry[Location] = _Location) ,

"@ComputerCount",

VAR _Country = [Country]

VAR _Location = [Location]

RETURN CALCULATE( COUNT(FactComputer[ID]), NOT(ISBLANK(FactComputer[Principal User])), DimCountry[Country] = _Country, DimCountry[Location] = _Location)

)

VAR _TotalCount =

SUMX(

_SummTbl,

[@ComputerCount] - [@UserCount]

)

RETURN SUMX( FILTER(_SummTbl, [Country] = _SelCountry),

IF([Country] = "Total", _TotalCount, [@ComputerCount] - [@UserCount])

)

This is the Data I used

IDShared DevicePrincipal UserPrinciple StateLocation

C1	A1	Active	Hildesheim
C1	A1	Active	Muenchen
C1	A1	Active	Miskolc
C1	A1	Active	Stuttgart
C1	A1	Active	Lerma
C1	A1	Active	Novi
C1	A1	Active	Drancy
C10	A1	Active	Stuttgart
C10	A1	Active	Hildesheim
C10	A1	Active	Muenchen
C10	A1	Active	Miskolc
C10	A1	Active	Lerma
C10	A1	Active	Novi
C10	A1	Active	Drancy
C11	A1	Active	Stuttgart
C11	A1	Active	Hildesheim
C11	A1	Active	Muenchen
C11	A1	Active	Miskolc
C11	A1	Active	Lerma
C11	A1	Active	Novi
C11	A1	Active	Drancy
C12	A1	Active	Stuttgart
C12	A1	Active	Hildesheim
C12	A1	Active	Muenchen
C12	A1	Active	Miskolc
C12	A1	Active	Lerma
C12	A1	Active	Novi
C12	A1	Active	Drancy
C13	A1	Active	Miskolc
C13	A1	Active	Stuttgart
C13	A1	Active	Hildesheim
C13	A1	Active	Muenchen
C13	A1	Active	Lerma
C13	A1	Active	Novi
C13	A1	Active	Drancy
C14	A1	Active	Stuttgart
C14	A1	Active	Hildesheim
C14	A1	Active	Muenchen
C14	A1	Active	Miskolc
C14	A1	Active	Lerma
C14	A1	Active	Novi
C14	A1	Active	Drancy
C15	A1	Active	Stuttgart
C15	A1	Active	Hildesheim
C15	A1	Active	Muenchen
C15	A1	Active	Miskolc
C15	A1	Active	Lerma
C15	A1	Active	Novi
C15	A1	Active	Drancy
C16	A1	Active	Stuttgart
C16	A1	Active	Hildesheim
C16	A1	Active	Muenchen
C16	A1	Active	Miskolc
C16	A1	Active	Lerma
C16	A1	Active	Novi
C16	A1	Active	Drancy
C17	A4	Active	Stuttgart
C17	A4	Active	Hildesheim
C17	A4	Active	Muenchen
C17	A4	Active	Miskolc
C17	A4	Active	Lerma
C17	A4	Active	Novi
C17	A4	Active	Drancy
C18	A5	Active	Stuttgart
C18	A5	Active	Hildesheim
C18	A5	Active	Muenchen
C18	A5	Active	Miskolc
C18	A5	Active	Lerma
C18	A5	Active	Novi
C18	A5	Active	Drancy
C2	A1	Active	Stuttgart
C2	A1	Active	Hildesheim
C2	A1	Active	Muenchen
C2	A1	Active	Miskolc
C2	A1	Active	Lerma
C2	A1	Active	Novi
C2	A1	Active	Drancy
C3	A1	Active	Stuttgart
C3	A1	Active	Hildesheim
C3	A1	Active	Muenchen
C3	A1	Active	Miskolc
C3	A1	Active	Lerma
C3	A1	Active	Novi
C3	A1	Active	Drancy
C4	A2	Active	Stuttgart
C4	A2	Active	Hildesheim
C4	A2	Active	Muenchen
C4	A2	Active	Miskolc
C4	A2	Active	Lerma
C4	A2	Active	Novi
C4	A2	Active	Drancy
C5	A1	Active	Stuttgart
C5	A1	Active	Hildesheim
C5	A1	Active	Muenchen
C5	A1	Active	Miskolc
C5	A1	Active	Lerma
C5	A1	Active	Novi
C5	A1	Active	Drancy
C6	A3	Active	Stuttgart
C6	A3	Active	Hildesheim
C6	A3	Active	Muenchen
C6	A3	Active	Miskolc
C6	A3	Active	Lerma
C6	A3	Active	Novi
C6	A3	Active	Drancy
C7	A1	Active	Hildesheim
C7	A1	Active	Muenchen
C7	A1	Active	Miskolc
C7	A1	Active	Stuttgart
C7	A1	Active	Lerma
C7	A1	Active	Novi
C7	A1	Active	Drancy
C8	A1	Active	Miskolc
C8	A1	Active	Stuttgart
C8	A1	Active	Hildesheim
C8	A1	Active	Muenchen
C8	A1	Active	Lerma
C8	A1	Active	Novi
C8	A1	Active	Drancy
C9	A1	Active	Stuttgart
C9	A1	Active	Hildesheim
C9	A1	Active	Muenchen
C9	A1	Active	Miskolc
C9	A1	Active	Lerma
C9	A1	Active	Novi
C9	A1	Active	Drancy

I hope people don't come after you for taking their spare computers away 😄

Re: Enter Total BAR in Bar Chart

ashwani_gupta — Wed, 14 Feb 2024 05:25:58 GMT

some_bih Please access this PBIX file via this link:

<<Link Removed>> as solution accepted in a later post.

Re: Enter Total BAR in Bar Chart

ashwani_gupta — Wed, 14 Feb 2024 05:24:37 GMT

talespin Yes, this approach has worked and I am able to get Total Bar in the graph. Many thanks.

Re: Enter Total BAR in Bar Chart

talespin — Wed, 14 Feb 2024 05:36:35 GMT

hi ashwani_gupta ,

You're welcome.