Forum Discussion

Suhel_Ansari's avatar
Suhel_Ansari
Icon for Helper V rankHelper V
8 months ago
Solved

Wrong Total in Table

Hi all, I am getting worng total, I am tryint to calcualte the price of Power BI Licsenes cost for PPU, PRO and Free users using the following table and dynamically calculate the total which I am get...
Need Help
  • Suhel_Ansari's avatar
    Suhel_Ansari
    8 months ago

    Hi GeraldGEmerick  - I got the answer using the ADDCOLUMNS  however I have to use the multiple ADDCOLUMNS can you please help me to optimze this measure. Thanks 

     

Royel's avatar
Royel
Icon for Super User rankSuper User
8 months ago

Hi Suhel_Ansari Since no sample file was shared, it’s difficult to write the exact DAX in one go. Please try the following and let me know how it goes.

License Cost = 
VAR CurrentCount = COUNT('Table'[EmailID])
VAR Multiplier = 
    SWITCH(
        SELECTEDVALUE('Table'[License]),
        "PBI_PREMIUM_PER_USER", 22.50,
        "Power BI Pro", 13.10,
        "Free Users", 13.10,
        BLANK()
    )
VAR RowResult = CurrentCount * Multiplier

// For Total row - iterate and calculate each license type
VAR TotalResult = 
    SUMX(
        VALUES('Table'[License]),
        VAR LicCount = CALCULATE(COUNT('Table'[EmailID]))
        VAR LicMultiplier = 
            SWITCH(
                'Table'[License],
                "PBI_PREMIUM_PER_USER", 22.50,
                "Power BI Pro", 13.10,
                "Free Users", 13.10,
                0
            )
        RETURN LicCount * LicMultiplier
    )

RETURN
IF(
    ISINSCOPE('Table'[License]),
    RowResult,
    TotalResult
)

 

Thanks 

 

Suhel_Ansari's avatar
Suhel_Ansari
Icon for Helper V rankHelper V
8 months ago

Hi Royel  - The meaure returns Blank and Zero as the Total result. Thanks