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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
eaj
Frequent Visitor

Share of grand total with parameters

I'm trying to build a self-service tool with three field parameters in a matrix: Row_param, column_param, and measures_param.

All measures work fine except 'Share of Total Patients'. I'm running into issues when my total (the divider) is constantly changing with the selection of parameters. How do I express the total in DAX?

My current measure:
Share of Grand Total (Patients) =
DIVIDE(
[Patients],
CALCULATE(
ALLSELECTED(dim_insurancecompany[name]),
ALLSELECTED(dim_date[week]),
[all other dimensions....],
ALLSELECTED(dim_item[articletype])
))

The columns in ALLSELECTED are the same as in my row and column parameters. I would like it to be calculated per column level. I've tried enabling that setting in the Power BI visual, but nothing happens. What am I doing wrong?

Current output:

eaj_0-1716281470418.png

 




2 REPLIES 2
amitchandak
Super User
Super User

@eaj , Nor very clear, Try like

 

Share of Grand Total (Patients) =
DIVIDE(
[Patients],
CALCULATE([Patients], allselected() ))

Im sorry for beeing unclear. I'll try again. I'm using this messure (full version):

Share of patients =
    DIVIDE([Patients],
    CALCULATE([Patients],
      Allselected(Dim_Date[Week]),
        Allselected(Dim_Date[MonthYear]),
        Allselected(Dim_Department[BusinessArea]),
        Allselected(Dim_Department[DepartmentDescription]),
        Allselected(Dim_Item[Clinic/Retail]),
        Allselected(Dim_Item[InvoiceGroup]),
        Allselected(Dim_Item[ItemCode]),
        Allselected(Dim_Item[ItemDescription]),
        Allselected(Fact_BillingInvoiceRows[Age]),
        Allselected(Dim_InsuranceCompany[InsuranceCompanyDescription]),
        Allselected(Fact_BillingInvoice[Ny kund]),
        Allselected(Fact_BillingInvoiceRows[Ny patient])
    ))

These are my parameters 
parameter_measures = {
    ("Fakturor CY", NAMEOF('_Measures'[Invoices CY]), 0),
    ("Fakturor LY", NAMEOF('_Measures'[Invoices LY]), 1),
    ("Fakturor vs LY", NAMEOF('_Measures'[Invoices vs LY]), 2),
    ("Omsättning CY", NAMEOF('_Measures'[Revenue CY]), 3),
    ("Omsättning LY", NAMEOF('_Measures'[Revenue LY]), 4),
    ("Omsättning vs LY", NAMEOF('_Measures'[Revenue vs LY]), 5),
    ("Patienter", NAMEOF('_Measures'[Patients]), 6),
    ("Patienter LY", NAMEOF('_Measures'[Patients LY]), 7),
    ("Patienter vs LY", NAMEOF('_Measures'[Patients vs LY]), 8),
    ("Andel Patienter", NAMEOF([Share of patients]), 8.5),
    ("Unika patienter CY", NAMEOF('_Measures'[Unique paitents CY]), 9),
    ("Unika patienter LY", NAMEOF('_Measures'[Unique patients LY]), 10),
    ("Unika patienter vs LY", NAMEOF('_Measures'[Unique patients vs LY]), 11)
}
 
parameters_columns = {
    ("Veckor", NAMEOF(Dim_Date[Week]), 1),
    ("Månader", NAMEOF(Dim_Date[MonthYear]), 2),
    ("Affärsområde", NAMEOF(Dim_Department[BusinessArea]), 3),
    ("Klinik", NAMEOF(Dim_Department[DepartmentDescription]), 4),
    ("Typ av försäljning(klinik/butik)", NAMEOF(Dim_Item[Clinic/Retail]), 5),
    ("Artikelgrupp", NAMEOF(Dim_Item[InvoiceGroup]), 6),
    ("Artikelkod", NAMEOF(Dim_Item[ItemCode]), 8),
    ("Artikelnamn", NAMEOF(Dim_Item[ItemDescription]), 9),
    ("Patientålder vid behandlingen", NAMEOF('Fact_BillingInvoiceRows'[Age]), 10),
    ("Försäkringsbolag", NAMEOF(Dim_InsuranceCompany[InsuranceCompanyDescription]), 11),
    ("Ny kund", NAMEOF(Fact_BillingInvoice[Ny kund]), 12),
    ("Ny patient", NAMEOF(Fact_BillingInvoice[Ny kund]), 13)
}

parameters_rows = {
    ("Veckor", NAMEOF(Dim_Date[Week]), 2),
    ("Månader", NAMEOF(Dim_Date[MonthYear]), 1),
    ("Affärsområde", NAMEOF(Dim_Department[BusinessArea]), 3),
    ("Klinik", NAMEOF(Dim_Department[DepartmentDescription]), 4),
    ("Typ av försäljning(klinik/butik)", NAMEOF(Dim_Item[Clinic/Retail]), 5),
    ("Artikelgrupp", NAMEOF(Dim_Item[InvoiceGroup]), 6),
    ("Artikelkod", NAMEOF(Dim_Item[ItemCode]), 8),
    ("Artikelnamn", NAMEOF(Dim_Item[ItemDescription]), 9),
    ("Patientålder vid behandlingen", NAMEOF('Fact_BillingInvoiceRows'[Age]), 10),
    ("Försäkringsbolag", NAMEOF(Dim_InsuranceCompany[InsuranceCompanyDescription]), 11),
    ("Ny kund", NAMEOF(Fact_BillingInvoice[Ny kund]), 12),
    ("Ny patient", NAMEOF(Fact_BillingInvoice[Ny kund]), 13)
}
Used in the self-service visuals like this:
eaj_0-1716283908672.png

The math is not incorrect, but I would like it to show the share between categories on column-basis. So each column sum to 100%. Right now the entire matrix sum to 100%.  

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel1

Power BI Monthly Update - May 2024

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