topic Re: How to calculate the %grand total by day in DAX Commands and Tips

How to calculate the %grand total by day

clarafff — Fri, 04 Nov 2022 10:58:56 GMT

Hi guys,

I am a green hand, just met a problem that I would like to get a help . I don't calculate the %GT sum of reg during whole period like below, I want to calculate by day, for exampler how much of percentage of affiliates share in 1/7 (100%), how should I write the dax ?

Re: How to calculate the %grand total by day

amitchandak — Sat, 05 Nov 2022 02:35:47 GMT

clarafff , % of Grand

Divide([Measure], calculate([Measure], allselected()) )

Or Sub Total

Divide([Measure], calculate([Measure], Filter(allselected(Table), Table[Day] = max(Table[Day]) ) )

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: How to calculate the %grand total by day

Anonymous — Mon, 07 Nov 2022 07:22:48 GMT

Hi clarafff ,

Create measure.

Percentage of affiliates per day: Measure_affiliates = var _count=COUNTX(FILTER(ALL('Table'),'Table'[REFERENCE_DATE]=MAX('Table'[REFERENCE_DATE])),[ACQUISITION_CHANNEL]) var _countaffiliates=COUNTX(FILTER(ALL('Table'),'Table'[REFERENCE_DATE]=MAX('Table'[REFERENCE_DATE])&&'Table'[ACQUISITION_CHANNEL]="affiliates"),[ACQUISITION_CHANNEL]) return DIVIDE( _countaffiliates,_count)

Measure_group = var _count=COUNTX(FILTER(ALL('Table'),'Table'[REFERENCE_DATE]=MAX('Table'[REFERENCE_DATE])),[ACQUISITION_CHANNEL]) var _countgroup=COUNTX(FILTER(ALL('Table'),'Table'[REFERENCE_DATE]=MAX('Table'[REFERENCE_DATE])&&'Table'[ACQUISITION_CHANNEL]=MAX('Table'[ACQUISITION_CHANNEL])),[ACQUISITION_CHANNEL]) return DIVIDE( _countgroup,_count)

Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: How to calculate the %grand total by day

clarafff — Mon, 07 Nov 2022 19:39:46 GMT

thanks! I got inspired by your dax.

% grand of total REG =

var test1 = CALCULATE(sum('table'[REG]),FILTER(ALL('Fact_Aquisition channel'),'tablel'[REFERENCE_DATE]=MAX('table'[REFERENCE_DATE])))

var test2 = CALCULATE(SUM('table'[REG]),FILTER(ALL('table'),'table'[REFERENCE_DATE]=Max('table'[REFERENCE_DATE])&&'table'[ACQUISITION_CHANNEL]=MAX('table'[ACQUISITION_CHANNEL])))

return

DIVIDE(test2,test1)