Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
deannapi
Frequent Visitor

Get column total and percent of column

I created a matrix of 2 categories and an ID column - the ID column will be used in the "Value" but as COUNT DISTINCT. 

 

deannapi_0-1708701567632.png

I want a measure to insert in between all columns of the matrix that show the % of each row

Example: 

 ColumnB Item1% of Total
ColumnA Item144/13 = 30.77%
ColumnA Item299/13 = 69.23%
Total13100%

Here is what I've tried and my values are grouped by ColumnA, ColumnB, and date.

 

% of Volume = 
VAR totalbyOrigin =
    SUMMARIZE (
        TableA,
        TableA[ColumnA],
        TableA[DateCreated],
        TableA[ColumnB],
        "% of Volume", DISTINCTCOUNT ( TableA[ID] )
    )
RETURN
    DIVIDE (
        totalbyOrigin,
        CALCULATE ( DISTINCTCOUNT ( TableA[ID] ), ALLSELECTED () )
    )

 

 I get the following error message:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

1 ACCEPTED SOLUTION
deannapi
Frequent Visitor

solving my own problem: 

% of Volume = 
VAR totalbyOrigin =
    SUMMARIZE (
        TableA,
        TableA[ColumnA],
        TableA[DateCreated],
        TableA[ColumnB],
        "Volume", DISTINCTCOUNT ( TableA[ID] )
    )
RETURN
    DIVIDE (
       SUMX( totalbyOrigin, [Volume]), 100
    )

THen I added this to my matrix and selected "percent of column".

View solution in original post

1 REPLY 1
deannapi
Frequent Visitor

solving my own problem: 

% of Volume = 
VAR totalbyOrigin =
    SUMMARIZE (
        TableA,
        TableA[ColumnA],
        TableA[DateCreated],
        TableA[ColumnB],
        "Volume", DISTINCTCOUNT ( TableA[ID] )
    )
RETURN
    DIVIDE (
       SUMX( totalbyOrigin, [Volume]), 100
    )

THen I added this to my matrix and selected "percent of column".

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors