What is the code of 'show value as percentage of grand total'

[jeongkim]

Hi,

Total value: 

Count of INVUNIT $dn = sumx(VALUES('INVUNIT'[inventoryUnitType]), [Count of INVUNIT $dn to AMIA/AMOB/FHCC by 2])

Row value: 

Parent Row: 'INVUNIT'[CT]

Sub Row: 'INVUNIT'[Location]

I wanna make right column with measure to add more functinos such Roundup cuz visual option doesn't offer Roundup. 

so desired output is 2 decimal numbers. 

e.g. 11.494% -> 11.50% with measure. 

[OwenAuger · Accepted solution]

Hi @jeongkim 

You can examine the DAX query generated by the visual using Performance Analyzer (see here for example).

Here is a sample query from a simple visual with % of Grand Total:

DEFINE
	VAR __DS0Core = 
		SUMMARIZECOLUMNS(
			ROLLUPADDISSUBTOTAL(ROLLUPGROUP('Table'[Column1], 'Table'[Column2]), "IsGrandTotalRowTotal"),
			"Total", 'Table'[Total],
			"A1", DIVIDE('Table'[Total], CALCULATE('Table'[Total], ALLSELECTED('Table'[Column1]), ALLSELECTED('Table'[Column2])))
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'Table'[Column1], 1, 'Table'[Column2], 1)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	[IsGrandTotalRowTotal] DESC, 'Table'[Column1], 'Table'[Column2]

The relevant line in this example is the one beginning "A1".

The measure is divided by the same measure wrapped in CALCULATE with ALLSELECTED modifier applied to the columns used in the visual.

So in this example I could create an explicit measure:

Total % of Grand Total =
DIVIDE (
    [Total],
    CALCULATE (
        [Total],
        ALLSELECTED ( 'Table'[Column1] ),
        ALLSELECTED ( 'Table'[Column2] )
    )
)

A similar pattern should apply to all % of Grand Total calculations.

All grouping fields in the visual would need to be included wrapped in ALLSELECTED within CALCULATE.

Hopefully that helps with your example! 🙂

Regards

[jeongkim]

Hi,

Can you please see the error I'm facing?

[OwenAuger]

Hi again @jeongkim 

From the DAX query, you just need to use the "A1" expression for your measure.

So your measure would be:

Count of INVUNIT $dn % =
DIVIDE (
    [Count of INVUNIT $dn],
    CALCULATE (
        [Count of INVUNIT $dn],
        ALLSELECTED ( 'INVUNIT'[CT] ),
        ALLSELECTED ( 'INVUNIT'[Location] )
    )
)

Regards 

[jeongkim]

Thanks it helps!