Helping to get the decimals out of only one column

[Anonymous]

Im using a formula in my matrix table but I need to keep the decimals in the total column and get rid of them in the other columns.

Is there a way to do that?

Avg Total 2 = 
VAR Months =
    CALCULATE(COUNTROWS( ALLSELECTED ( PBI[Fecha];PBI[D_Laborales] ));PBI[D_Laborales]<>7)
RETURN
    IF (
        ISINSCOPE ( PBI[Fecha] );
        SUM ( PBI[Total] );
        DIVIDE (SUM ( PBI[Total]); Months ))

Is there a way to do that?

Sample DATA:

https://www.dropbox.com/s/pvr3htf4c3m3e0c/BI%20VISITAS%20DEMO%20forum.pbix?dl=0

[v-juanli-msft]

Hi @Anonymous 

Create a measure

Avg Total 2.2 = 
    IF (
        HASONEVALUE( PBI[Fecha] ),
        IF([Avg Total 2]<>BLANK(),FORMAT([Avg Total 2],"0")),
       IF([Avg Total 2]<>BLANK(), FORMAT([Avg Total 2],"0.00"))
       )

But it is a measure which is of type Text, you can use the [Avg Total 2] for other calculations.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

hI @v-juanli-msft why it gives "0" in the fourth line? it should be 0,14

and it is possible to get a cero insteadoof  empty spaces.

Becasue there is something important Id like to see in this matrix, which is to show always everybody from the list even if they have no values in any days.

Please help me with that... it is really hard for me to understand the logic of what you did and find a solution by my self.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

After changing your measure as below

Avg Total 2.23 = VAR Months =
    CALCULATE(COUNTROWS( ALLSELECTED ( PBI[Fecha],PBI[D_Laborales] )),PBI[D_Laborales]<>7)
RETURN
    IF (
        ISINSCOPE ( PBI[Fecha] ),
        IF(SUM(PBI[Total])<>BLANK(),FORMAT(SUM ( PBI[Total] ),"0")),
        DIVIDE (SUM ( PBI[Total]), Months ))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[natabird3]

Try using the Format function:

FORMAT( 12345.67, "General Number") 
FORMAT( 12345.67, "Currency") 
FORMAT( 12345.67, "Fixed") 
FORMAT( 12345.67, "Standard") 
FORMAT( 12345.67, "Percent") 
FORMAT( 12345.67, "Scientific")