Re: Finding how often a value appears in in a mont...

mcash1 · ‎12-20-2022

Hello,

I'm new to Power BI and writing DAX commands and I'm having trouble finding an answer to this on here, I'm trying to figure out a formula for showing the percentage of monthly submissions that discuss a specific topic. Here's what happens:

Managers file submissions for each meeting with a teammate
Each submission records the different topics discussed (7 different topics total, selection can include multiple topics)
Each month, I want to look at the number of times a topic was discussed compared to the overall submissions that month

I want to make a line chart showing the monthly % for each topic, and not 100% stacked as the totals will equal more than 100%. E.g. in November, 5/10 sessions discussed Topic 1 (50%) and 8/10 sessions discussed Topic 2 (80%), so I want a line chart showing the monthly values of each topic. And there will be slicers for the topics, so the chart should update based on slicer selected.

Here is an example of what the data looks like, I'm having trouble creating that final column

Session ID	Date	Topics Discussed
1	Nov	Topic A
1	Nov	Topic B
1	Nov	Topic C
2	Nov	Topic A
2	Nov	Topic B
3	Nov	Topic A
3	Nov	Topic B
3	Nov	Topic C
3	Nov	Topic D

Looking for something like this:

Month	Topic	Total Sessions Discussed
Nov	Topic A	85%
Nov	Topic B	60%
Nov	Topic C	10%
Dec	Topic A	90%
Dec	Topic B	75%
Dec	Topic C	5%

SamInogic · ‎12-20-2022

Hi,

We have go through your requirement and it seems that you want to calculate the Sessions in Percentage categorized by Month and Topics. Therefore, you can try below mentioned steps to achieve the same.

Create a new table with Grouping the data based on Topic and Month columns as shown in below,

New Summary = GROUPBY(Sessions, Sessions[Month], Sessions[Topic],"Total Sessions", COUNTAX(CURRENTGROUP(), Sessions[Session ID]))
Now you can create a column to calculate the Percentage of Total Sessions with below DAX expression and change the Format of the Column as Percentage.

% Sessions = 'New Summary'[Total Sessions] / 10

If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.

Thanks!

Inogic Professional Services

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service: http://www.inogic.com/services/

Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/

Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/

mcash1 · ‎12-21-2022

Hello! Thank you so much for replying! This has been very helpful but I need a little more info.

I created the NEW SUMMARY table as stated, which works great. But the % Sessions column needs a little fixing. % Sessions = 'New Summary'[Total Sessions] / 10 (I need the "10" to be replaced by the monthly total session. I took a screenshot of the actual data (naming is different than my question above.)
On the left, it shows the % Session using the "10" as monthly totals, but I need the totals as shown on the right table.

(I filtered to show just one topic, to make it easier to see)

Here's the end result I'm looking for:

Do I need to add another column to the NEW SUMMARY table to count the number of monthly submissions that the % Session can use to calculate the average?

SamInogic · ‎12-21-2022

Hi,

In order have dynamic Total Number of Sessions by Month to calculate the % of Sessions then you will need first to store Total Number of sessions by month in new Table with DAX as below:

Total Sessions in Each Month = GROUPBY('New Summary', 'New Summary'[Sessions_Month], "Total Monthly Sessions", SUMX(CURRENTGROUP(), 'New Summary'[Total Sessions]))

Then within New Summary table, update the DAX for “% Sessions” column as below:

% Sessions = 'New Summary'[Total Sessions] / LOOKUPVALUE('Total Sessions in Each Month'[Total Monthly Sessions], 'Total Sessions in Each Month'[New Summary_Sessions_Month], 'New Summary'[Sessions_Month])