Percentage of Grand Total from multiple datasets

[RLSid17]

Hi,

Would it be possible to count the values of 1 column from multiple datasets (currently 9 datasets)?

I'm currently creating a report for survey responses and each dataset/category has a column "Grouped Response" so it would count how many are unfavorable, neutral, and favorable and get the percentage of it.

So I managed to get the percentage from 1 dataset (see below sample) but I was tasked to get the percentage of all the categories. 

I'm still a novice in Power BI and learning as I go. Any response or solution is much appreciated! Let me know if there are more details needed from me.

[v-yadongf-msft]

Hi @RLSid17 ，

Did you mean this?

Best regards,

Yadong Fang

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

[RLSid17]

Hi @v-yadongf-msft 

No, it is not the visualization of it. So basically whatever that was selected in the slicer per table as seen below would automatically calculate the visual:

Just wondering if it is possible. Then if not I will just use the slicer for the UNIONed table. 🙂

[v-yadongf-msft]

Hi @RLSid17 ,

I think it is possible. When you use the slicer, the table will be filtered by the slicer and calculate automatically. You can try it.

Best regards,

Yadong Fang

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

[v-yadongf-msft]

Hi @RLSid17 ,

I have added week number column to my test tables like below:

Please try:

NewTable2 = UNION(
    SELECTCOLUMNS('Table1',"Employee Favorability",'Table1'[Employee Favorability],"Week number",'Table1'[Week number]),
    SELECTCOLUMNS('Table2',"Employee Favorability",'Table2'[Employee Favorability],"Week number",'Table2'[Week number])
    )

I think this is the result you want:

Best regards,

Yadong Fang

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

[RLSid17]

Thank you! This works but would it be possible to count the values from each slicer of the table, not the UNIONed table? This may be my last query. I really appreciate your assistance on this!

[v-yadongf-msft]

Hi @RLSid17 ,

I have two test tables with common columns.

Table1:

 Table2:

You can merge two tables with following DAX:

NewTable = UNION('Table1','Table2')

You will get a new table like this:

Then you can count the values of 1 column from multiple datasets.

If I misunderstand your demands, please feel free to contact us in time.

Best regards,

Yadong Fang

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

[RLSid17]

Hi @v-yadongf-msft 

Thank you for your response! I have tried what you suggested but an error occurred:

Each of the tables has a different number of columns, is there a way to use the UNION function with only 1 column for each table?

[v-yadongf-msft · Accepted solution]

Hi @RLSid17 ,

If your datasets have different columns, please try:

NewTable2 = UNION(
    SELECTCOLUMNS('Table1',"Employee Favorability",'Table1'[Employee Favorability]),
    SELECTCOLUMNS('Table2',"Employee Favorability",'Table2'[Employee Favorability])
    )

Please refer the attached pbix file.

Best regards,

Yadong Fang

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

[RLSid17]

Yes, this works! Thank you!

But there is one problem and I forgot to mention it initially, apologies for that. I have here the report that I'm doing:

So the one you suggested, it works but it counts all the data which is expected. But I wanted it to be filtered, for example, when I select the week number it will change the number of responses and also the calculation of the employee favorability. I apologize if it is confusing and this is a lot more complex than expected for me.

Is there a way to count the values when it is filtered?