Comparing numeric values with count rows value

Fusilier2 · ‎08-16-2024

Hi,

Don't know if this is possible but hope somebody can help/advise.

I have data composed of answers to responses from a local survey. The data consists of one answer per respondent per question (Agree, disagree etc.) So my visuals are set up to count rows. All fine.

However I have a second set of National data where the data consists of the total number of responses for the questions (e.g. 5,400 said Agree).

Is there any way of using both sets of data in a PBI report so that both sets of data can appear on the same visual - say as % of total?

Can't think of a way to do this (or if it is even possible) so may have to have each type of data in a different visual.

Fusilier · ‎08-17-2024

I think I was over thinking things.

If I both types of data, but one as sum and the other as count I can use both on the same chart.

Ritaf1983 · ‎08-16-2024

Hi @Fusilier2

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Fusilier · ‎08-16-2024

Hi,

Here is some dummy data.

Two tables.

Table one is individual responses for the local area, which I would use 'countrows' to total in any measures:

Table two is the national data which is a total of all responses as opposed to individual responses:

Year	Question	Answer	Total answer	Question matched
01/01/23	Question 1. Overall, how satisfied or dissatisfied are you with your local area as a place to live?	Fairly satisfied	25	Q4. Overall on behalf of your household, how satisfied or dissatisfied are you with your local area as a place to live?
01/01/23	Question 1. Overall, how satisfied or dissatisfied are you with your local area as a place to live?	Very satisfied	25	Q4. Overall on behalf of your household, how satisfied or dissatisfied are you with your local area as a place to live?
01/01/23	Question 1. Overall, how satisfied or dissatisfied are you with your local area as a place to live?	Neither satisfied nor dissatisfied	52	Q4. Overall on behalf of your household, how satisfied or dissatisfied are you with your local area as a place to live?
01/01/23	Question 1. Overall, how satisfied or dissatisfied are you with your local area as a place to live?	Fairly dissatisfied	60	Q4. Overall on behalf of your household, how satisfied or dissatisfied are you with your local area as a place to live?
01/01/23	Question 1. Overall, how satisfied or dissatisfied are you with your local area as a place to live?	Very dissatisfied	45	Q4. Overall on behalf of your household, how satisfied or dissatisfied are you with your local area as a place to live?

'Question matched' is the same in both tables.

I would like to be able to chart each in the same visual as a total or % of total (e.g. a line for 'Very satisfied' for 'Local area' and a line for 'Very satisfied' 'Total national', but obviously the granularity is different in both tables.

If you need any more explanation, please let me know.

Anonymous · ‎08-21-2024

Hi @Fusilier2 ,

Thanks for your providing sample data. Base on your description, it seems like you want to display the count and %of total on the same visual? Could you please provide the calculation logic of %of total and your visual settings base on your shared sample data?

In addition, you can review the following links about getting the percent of total...

How to Calculate Percent of Total in Power BI (With Example)

Percentage of Total in Power BI: A Visual Guide

Solved: Calculating percentage from two different tables i... - Microsoft Fabric Community

Best Regards