Enter Total BAR in Bar Chart

[ashwani_gupta]

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?

[talespin · Accepted solution]

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	0	A1	Active	Hildesheim
C1	0	A1	Active	Muenchen
C1	0	A1	Active	Miskolc
C1	0	A1	Active	Stuttgart
C1	0	A1	Active	Lerma
C1	0	A1	Active	Novi
C1	0	A1	Active	Drancy
C10	0	A1	Active	Stuttgart
C10	0	A1	Active	Hildesheim
C10	0	A1	Active	Muenchen
C10	0	A1	Active	Miskolc
C10	0	A1	Active	Lerma
C10	0	A1	Active	Novi
C10	0	A1	Active	Drancy
C11	0	A1	Active	Stuttgart
C11	0	A1	Active	Hildesheim
C11	0	A1	Active	Muenchen
C11	0	A1	Active	Miskolc
C11	0	A1	Active	Lerma
C11	0	A1	Active	Novi
C11	0	A1	Active	Drancy
C12	0	A1	Active	Stuttgart
C12	0	A1	Active	Hildesheim
C12	0	A1	Active	Muenchen
C12	0	A1	Active	Miskolc
C12	0	A1	Active	Lerma
C12	0	A1	Active	Novi
C12	0	A1	Active	Drancy
C13	0	A1	Active	Miskolc
C13	0	A1	Active	Stuttgart
C13	0	A1	Active	Hildesheim
C13	0	A1	Active	Muenchen
C13	0	A1	Active	Lerma
C13	0	A1	Active	Novi
C13	0	A1	Active	Drancy
C14	0	A1	Active	Stuttgart
C14	0	A1	Active	Hildesheim
C14	0	A1	Active	Muenchen
C14	0	A1	Active	Miskolc
C14	0	A1	Active	Lerma
C14	0	A1	Active	Novi
C14	0	A1	Active	Drancy
C15	0	A1	Active	Stuttgart
C15	0	A1	Active	Hildesheim
C15	0	A1	Active	Muenchen
C15	0	A1	Active	Miskolc
C15	0	A1	Active	Lerma
C15	0	A1	Active	Novi
C15	0	A1	Active	Drancy
C16	0	A1	Active	Stuttgart
C16	0	A1	Active	Hildesheim
C16	0	A1	Active	Muenchen
C16	0	A1	Active	Miskolc
C16	0	A1	Active	Lerma
C16	0	A1	Active	Novi
C16	0	A1	Active	Drancy
C17	0	A4	Active	Stuttgart
C17	0	A4	Active	Hildesheim
C17	0	A4	Active	Muenchen
C17	0	A4	Active	Miskolc
C17	0	A4	Active	Lerma
C17	0	A4	Active	Novi
C17	0	A4	Active	Drancy
C18	0	A5	Active	Stuttgart
C18	0	A5	Active	Hildesheim
C18	0	A5	Active	Muenchen
C18	0	A5	Active	Miskolc
C18	0	A5	Active	Lerma
C18	0	A5	Active	Novi
C18	0	A5	Active	Drancy
C2	0	A1	Active	Stuttgart
C2	0	A1	Active	Hildesheim
C2	0	A1	Active	Muenchen
C2	0	A1	Active	Miskolc
C2	0	A1	Active	Lerma
C2	0	A1	Active	Novi
C2	0	A1	Active	Drancy
C3	0	A1	Active	Stuttgart
C3	0	A1	Active	Hildesheim
C3	0	A1	Active	Muenchen
C3	0	A1	Active	Miskolc
C3	0	A1	Active	Lerma
C3	0	A1	Active	Novi
C3	0	A1	Active	Drancy
C4	0	A2	Active	Stuttgart
C4	0	A2	Active	Hildesheim
C4	0	A2	Active	Muenchen
C4	0	A2	Active	Miskolc
C4	0	A2	Active	Lerma
C4	0	A2	Active	Novi
C4	0	A2	Active	Drancy
C5	0	A1	Active	Stuttgart
C5	0	A1	Active	Hildesheim
C5	0	A1	Active	Muenchen
C5	0	A1	Active	Miskolc
C5	0	A1	Active	Lerma
C5	0	A1	Active	Novi
C5	0	A1	Active	Drancy
C6	0	A3	Active	Stuttgart
C6	0	A3	Active	Hildesheim
C6	0	A3	Active	Muenchen
C6	0	A3	Active	Miskolc
C6	0	A3	Active	Lerma
C6	0	A3	Active	Novi
C6	0	A3	Active	Drancy
C7	0	A1	Active	Hildesheim
C7	0	A1	Active	Muenchen
C7	0	A1	Active	Miskolc
C7	0	A1	Active	Stuttgart
C7	0	A1	Active	Lerma
C7	0	A1	Active	Novi
C7	0	A1	Active	Drancy
C8	0	A1	Active	Miskolc
C8	0	A1	Active	Stuttgart
C8	0	A1	Active	Hildesheim
C8	0	A1	Active	Muenchen
C8	0	A1	Active	Lerma
C8	0	A1	Active	Novi
C8	0	A1	Active	Drancy
C9	0	A1	Active	Stuttgart
C9	0	A1	Active	Hildesheim
C9	0	A1	Active	Muenchen
C9	0	A1	Active	Miskolc
C9	0	A1	Active	Lerma
C9	0	A1	Active	Novi
C9	0	A1	Active	Drancy

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

[ashwani_gupta]

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

[talespin]

hi @ashwani_gupta ,

You're welcome.

[some_bih]

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

[ashwani_gupta]

@some_bih Please access this PBIX file via this link: 

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

[some_bih]

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

[ashwani_gupta]

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

[some_bih]

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

Still, try to replace 

ALLSELECTED with VALUES

Otherwise share pbi file

[ashwani_gupta]

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