Create a DAX measure that calculates the percentage by gender of the Column total

PBIfolks — Tue, 07 Mar 2023 14:49:23 GMT

Hello Community Members,

Need some help with DAX.

Datasets showing last year's report. I need to create a DAX measure that calculates the percentage by gender of the Column total (in Yellow marked value in the screenshot). Somehow, the mind got faded, not getting the correct solution.

I've created a DAX but giving error message (The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.).

Attached is the PBIX file.

Total % = VAR curmonth = month(max('TBL_L R'[Date])) VAR curyear = year(max('TBL_L R'[Date])) VAR gender = SELECTEDVALUE('TBL_L R'[Gender]) VAR Female = countx(filter('TBL_L', year('TBL_L'[Date])=curyear && month('TBL_L'[Date])=curmonth && 'TBL_L'[Gender]=gender && 'TBL_L'[Gender] = "Female" ), TBL_L[Gender]) VAR Male = countx(filter('TBL_L', year('TBL_L'[Date])=curyear && month('TBL_L'[Date])=curmonth && 'TBL_L'[Gender]=gender && 'TBL_L'[Gender] = "Male" ), TBL_L[Gender]) VAR Total = IF(SELECTEDVALUE ( 'TBL_L'[Gender] ) = "Female", DIVIDE(Female, Female+Male), ALLEXCEPT('TBL_L R','TBL_L R'[Gender])) && IF(SELECTEDVALUE ( 'TBL_L'[Gender] ) = "Male", DIVIDE(Male, Female+Male), ALLEXCEPT('TBL_L R','TBL_L R'[Gender])) RETURN Total

Please help me to either tweak or create a new one.

Many Thanks

Re: Create a DAX measure that calculates the percentage by gender of the Column total

BITomS — Tue, 07 Mar 2023 15:23:18 GMT

Hi PBIfolks,

I think this can be simplified:

1) create a simple measure to count the male/female column i.e. Count = count('TBL_L R'[Gender]); then

2) create another measure for the % i.e. % Measure = [Count]/calculate(count('TBL_L R'[Date]),allexcept('TBL_L R','TBL_L R'[Gender]))

This ensures it is only creating the percentage by your column (gender) which is what it looks like you are trying to achieve.

It would be advisable to have a date dimension table joined to your fact table via the date field and use the date from this table as your row field in your matrix.

Hope this helps.

topic Create a DAX measure that calculates the percentage by gender of the Column total in DAX Commands and Tips

Create a DAX measure that calculates the percentage by gender of the Column total

Re: Create a DAX measure that calculates the percentage by gender of the Column total