Convert Billion to Million in calculated table and add suffix M for the converted value

[deb_power123]

Hi All,

Below is an input table with AuditDate, Score ,school name and Percentage students.

AuditDate	Score	SchoolName	PercentageStudents
15.08.2021	-2	A	20%
15.08.2021	-1	B	30%
16.08.2021	3	C	22%
16.08.2021	0	D	45%
16.08.2021	-1	A	65%
17.08.2021	-1	B	17%
17.08.2021	-1	C	29%
18.08.2021	3	A	78%
18.08.2021	0	C	87%
19.08.2021	-1	C	22%
19.08.2021	3	D	45%
19.08.2021	0	E	65%
20.08.2021	-1	B	17%
21.08.2021	2	D	29%
22.08.2021	1	E	22%
22.08.2021	-1	A	45%
1.09.2021	0	E	65%
1.09.2021	-1	B	17%
2.09.2021	2	D	29%
3.09.2021	1	E	22%
3.09.2021	-1	A	45%
4.09.2021	1	C	65%
5.09.2021	1	C	17%
5.09.2021	3	A	29%

I have the below calculated table to show current month, catefgory and Paramscore columns  as shown in below output. paramscore is the Average aggregate of the score and percentage students column in the input source table above.

We have created two categories to store these values in a column paramscore for respective month,year.

Calculated table :

NewTable = 
GENERATE (
    SUMMARIZE (
        SELECTCOLUMNS (
            'Table',
            "CurrentMonth", FORMAT ( 'Table'[AuditDate], "MMMM - yyyy" )
        ),
        [CurrentMonth]
    ),
    ADDCOLUMNS (
        DATATABLE (
            "Category", STRING,
            {
                { "AverageScore" },
                { "AveragePercentage" }
            }
        ),
        "ParamScore",
            VAR _m = [CurrentMonth]
            RETURN
                IF (
                    [Category] = "AverageScore",
                    FORMAT (
                        CALCULATE (
                            AVERAGE ( 'Table'[Score] ),
                            FORMAT ( 'Table'[AuditDate], "MMMM - yyyy" ) = _m
                        ),
                        ""
                    ),
                    FORMAT (
                        CALCULATE (
                            AVERAGE ( 'Table'[PercentageStudents] ),
                            FORMAT ( 'Table'[AuditDate], "MMMM - yyyy" ) = _m
                        ),
                        "0%"
                    )
                )
    )
)

Expected Output:

Here the score is in unit of  Billion but I want to convert and show the value in Million and put M as a suffix. In ParamScore column, I want to display 0.1875  and 0.75  to 187.5 M and 750 M respectively.

Is it possible and if so please suggest the changes in the above formula.

Present Output:

Kind regards

Sameer

[TheoC]

Hi @deb_power123 

Can you add a Calculated Column to NewTable? Maybe something like "Calculated Column = IF ( NewTable[Category] = "AverageScore" , NewTable[ParamScore] * 100 & "M" , NewTable[ParamScore] )"

Apologies for syntax errors. On phone.

All the best.

Theo

[deb_power123]

Hi @TheoC 

Thankyou for the approach, I will like to extend your suggestions a little further. I also want to add a percentage sign using your approach for AveragePercentage column. Assuming we dont have % sign as shown in my screenshot. How can I append it in the below code you suggested?

I tried to modify your code a further as below in blue font but it threw error.

Could you suggest , please. How can we loop both these changes in your provided calculated column approach.

Calculated Column = IF ( NewTable[Category] = "AverageScore" , NewTable[ParamScore] * 100 & "M" , NewTable[ParamScore]

IF ( NewTable[Category] = "AveragePercentage" , NewTable[ParamScore]  & "%" , NewTable[ParamScore]

)

[TheoC · Accepted solution]

Hi @deb_power123 

Calculated Column = IF ( NewTable[Category] = "AverageScore" , NewTable[ParamScore] * 100 & "M" , IF ( NewTable[Category] = "AveragePercentage" , NewTable[ParamScore] & "%", NewTable[ParamScore] ) )

Hope this works for you 🙂

[deb_power123]

I accepted the solution , its working but I have a last query for which I will PM you  as per your suggestions

[deb_power123]

Hi @TheoC

It certainly worked.I have a last question.Sorry for asking a lot of questions. I also want to create a calculated column or measure which has the difference of the paramscore of the latest month and paramscore of the previous month. Assuming August 2021 is our latest month and September 2021 is the previous month because in real case scenario there are 8 to 9 months so it should find the latest and then substract the value of the previous month from that. 

Could you please suggest a calculated column or measure which can find the difference ?

Average_Score_diff  = 0.1875 -0.750 = -0.5625

Average_Percentage_difference = 40% - 36% = 4%

Could you please suggest?I think this will be my last question in this topic.

Kind regards

Sameer

[TheoC]

Hi @deb_power123 

Can I ask that you mark the answer as the solution so that the Community has access to the answer when they search and if you send me a PM, I can assist with the additional queries?

Thanks so much for understanding and I appreciate you letting me know it worked!

I'll respond via PM when I get in front on computer.  On phone atm.

Thanks heaps!

Theo