comparing values based on slicers

[Anonymous]

Hi,

I want to display a company's ranking count and it's competitors count for different products and subproducts in 100% stacked bar chart. When a user pass a particular company name through parameter, the ranking count for this company and it's competitors should be calculated based on the values selected in multiple slicers by the user. The competitors nos. should not include the selected company and use rank in the legend. 

I have two challenges to achieve this:

1. How to calculate ranking count for company and competitors based on the values selected in the slicers

2. How to display them on a single visual (100% stacked bar chart)

In the below screenshot, the visual in the lower section is ideal to display the information but it is comingup with a paid license not a free visual:

Thanks

[v-yanjiang-msft]

Hi @Anonymous ,

Sorry I can't reproduce your sample, could you please provide more details about your sample? Companies are in the column Company Name, what chout competitors, are they in the same table in another column? Then how to  distinguish the value of company and competitor.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yanjiang-msft ,

Yes, companies  are in column Company Name and competitors are also in the same column. The Company Name will be provided through a parameter and competitors will be considered based on the selected values in the slicers within filter criteria and will not include the company against which we are going to compare.

Also, this report will be embedded within a sharpoint site and Client name parameter will be passed at there only.

the below is the sample data:

CompanyRankProductSub ProductsCountryPriceNo. of Customers

A	Bad	Phone	Battery	India	30000	250000
B	Good	TV	Headphone	US	5000	50000
C	Excellent	TV	Speaker	Germany	60000	134000
D	Good	TV	Speaker	Germany	70000	300000
A	Excellent	Phone	Battery	India	12000	20000
B	Bad	TV	Speaker	India	30000	90000
C	Good	Phone	Phone Cover	US	3000	120000
D	Excellent	Phone	Phone Cover	Germany	90000	230000
A	Good	TV	Headphone	Germany	50000	34000
B	Bad	Phone	Phone Cover	India	80000	250000
C	Good	TV	Headphone	US	7000	70000
D	Excellent	Phone	Phone Cover	Germany	13000	170000
C	Excellent	TV	Speaker	Germany	60000	134000
D	Good	TV	Speaker	Germany	70000	300000
A	Excellent	Phone	Battery	India	12000	20000
B	Bad	TV	Speaker	India	30000	90000
C	Good	Phone	Phone Cover	US	3000	120000
A	Excellent	Phone	Battery	India	12000	20000
B	Bad	TV	Speaker	India	30000	90000
C	Good	Phone	Phone Cover	US	3000	120000
D	Excellent	Phone	Phone Cover	Germany	90000	230000
A	Good	TV	Headphone	Germany	50000	34000
B	Bad	Phone	Phone Cover	India	80000	250000
C	Good	TV	Headphone	US	7000	70000
D	Excellent	Phone	Phone Cover	Germany	13000	170000
C	Excellent	TV	Speaker	Germany	60000	134000
B	Bad	TV	Speaker	India	30000	90000
C	Good	Phone	Phone Cover	US	3000	120000
A	Excellent	Phone	Battery	India	12000	20000
B	Bad	TV	Speaker	India	30000	90000
C	Good	Phone	Phone Cover	US	3000	120000
D	Excellent	Phone	Phone Cover	Germany	90000	230000
A	Good	TV	Headphone	Germany	50000	34000
B	Bad	Phone	Phone Cover	India	80000	250000
C	Good	TV	Headphone	US	7000	70000
D	Excellent	Phone	Phone Cover	Germany	13000	170000
C	Excellent	TV	Speaker	Germany	60000	134000
D	Good	TV	Speaker	Germany	70000	300000
A	Excellent	Phone	Battery	India	12000	20000
B	Bad	TV	Speaker	India	30000	90000
C	Good	Phone	Phone Cover	US	3000	120000
A	Excellent	Phone	Battery	India	12000	20000
B	Bad	TV	Speaker	India	30000	90000
C	Good	Phone	Phone Cover	US	3000	120000
D	Excellent	Phone	Phone Cover	Germany	90000	230000
A	Good	TV	Headphone	Germany	50000	34000
B	Bad	Phone	Phone Cover	India	80000	250000
C	Good	TV	Headphone	US	7000	70000
D	Excellent	Phone	Phone Cover	Germany	13000	170000
C	Excellent	TV	Speaker	Germany	60000	134000

Thanks

[v-yanjiang-msft]

Hi @Anonymous ,

According to your description, here's my solution.

1.Create a new table.

Table 2 = VALUES('Table'[Company])

There's no relationship of the two tables.

2.Create a measure.

Check = IF(SELECTEDVALUE('Table 2'[Company])=MAX('Table'[Company]),1,0)

3.For the company visual, put Product and Company in Axis, Rank in legend and values, put the measure in the visual filter and set its value is 1.

The same for the competitor visual, but set the measure is 0.

 4.Put the company column in the new table into a slicer, then get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @v-yanjiang-msft  for the soultion above. 

Actually i had mentioned about 2 challlanges above in the question and it solves the 1st one. My 2nd challenge is still remaining as how to dispaly them (Client and Peer's values) within a single visual - 100% stacked bar chart.

Addititonaly, although it is not a part of my initial question, how to calculate the measures if selected value is coming through a parameter in embeded report in sharepoint portal rather than slicer.

Aprreciate for the help.

Thanks

[v-yanjiang-msft]

Hi @Anonymous ，

By my test, I don't think you can dispaly them (Client and Peer's values) within a single visual, as the filter of them(Client and Peer's values) are different. I tried to create a competitor measure, but multiple values cann't be calculate by a measure.

About using the parameter in other report, as long as it has no relationship with the above sample data table, you can modify the formula according to your situation.

Check= IF(SELECTEDVALUE('Table3'[Parameter])=MAX('Table'[Company]),1,0)

If your model is more complex, could you please upload your pbix file without sensitive information for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yanjiang-msft ,

Here is a the sample data. 

Also, I found one custom visual multi axes chart by xViz. this visual is helping me to present client and competitors values on single visual along with legend. Please recommend for this visual also if i can go with it as it seems paid one.

I am using these measure for client and compeitors values:

Client Grade =

var parameter = SELECTEDVALUE(clientvscompsample[Client Name],BLANK())

var ClientGrade = CALCULATE(COUNT(clientvscompsample[Grade])

,clientvscompsample[Client Name]= parameter)

Return

ClientGrade

Competitors Grade =

var parameter = SELECTEDVALUE(clientvscompsample[Client Name],BLANK())

var CompGrade = CALCULATE(COUNT(clientvscompsample[Grade])

,clientvscompsample[Client Name]= parameter)

Return

CompGrade

Client Name	Survey Area	Task	Sub Task	Grade
ABC	Operations	Maintenance	Closing	Top
ABC	Operations	Maintenance	Opening	Top
ABC	HR	Payroll	Oversight	Top
ABC	HR	Payroll	Processing	Top
BEF	TFD	Financial	Drafts	High
BEF	TFD	Financial	Drafts	Low
XYZ				High
XYZ	security	Tsecurity	PTsecurity	Ordinary
XYZ	HR	Payroll	System Access	High
PST	security	Tsecurity	PTsecurity	Abuse
PQR	HR	Payroll	PA Reporting	High
PQR	HR	Payroll	Oversight	High
PQR	HR	Payroll	Processing	High
RST	Banking	ACH	Set-up	High
RST	Banking	ACH	Processing	High
RST	Banking	Self	Audit	High
PVT	Financial	Institution	Remediation	High
RMS	Accounting	Reporting	Monitoring	Abuse
SPS	Accounting	Accounting	Accounts Payable	High

[v-yanjiang-msft]

Hi @Anonymous ,

By your formula, it always gets the same count value of Client Grade and Competitors Grade.

I can import the Multiple Axes Chart-XViz visual successfully, just click Get more visuals and enter the visual name then click Add. 

If you cann't import it , is your Power BI account free license? Try to upgrade it to Pro trial and reimport again.

Best Regards,
Community Support Team _ kalyj