Multiply count of values in a column with a calculated column

Pharms — Tue, 03 Oct 2023 15:19:20 GMT

Apologies if this seems a bit long winded, I am not the greatest BI user, but thought I had been able to do this. I hope I am putting enought detail in. I am using a SQL server as my data source with lots of interconnected tables through the modelling. I have generated some data which is correct.

The first table shows different classes and 'periods' is a count of number of times that code appears in a table. It is the values part of a matrix. I used the count of function in the fields settings, not DAX.

Second one is similar but counting a different value in the same way, 'pupils' is a count of the number of IDs for a specific class code.

The third table is a multiplication of the values of the previous two tables, using

Commitments by class v3 = SUMX ( VALUES ( 'Timetable Schedule'[txtCode] ), CALCULATE(COUNTA('Set Lists'[txtSchoolID])) * CALCULATE(COUNTA('Timetable Schedule'[txtCode])) )

Took me ages (noob), but that all works. What I want to do now is find a value for each class that is number of pupils (the value in the second table) multiplied by a fixed value that I used a calculated column to assign to each group. Basically how may reports they get each year

Reports per year = IF(CONTAINSSTRING([txtCode], "9"), "6", IF(CONTAINSSTRING([txtCode], "10"), "6", IF(CONTAINSSTRING([txtCode], "11"), "4",IF(CONTAINSSTRING([txtCode], "12"), "6",IF(CONTAINSSTRING([txtCode], "13"), "4", "")))))

I tried to edit my sumx dax, but no joy.

Any help massively appreciated.

Re: Multiply count of values in a column with a calculated column

lbendlin — Thu, 05 Oct 2023 00:00:15 GMT

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

topic Re: Multiply count of values in a column with a calculated column in DAX Commands and Tips

Multiply count of values in a column with a calculated column

Re: Multiply count of values in a column with a calculated column