Wrong Total in Table

[Suhel_Ansari]

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 getting wrong or null as total, please asssit. Thanks

Measure =

    VAR Total = CALCULATE( COUNT('Table'[EmailID]))

    VAR PPU =  IF(SELECTEDVALUE('Table'[License]) = "PBI_PREMIUM_PER_USER" , Total * 22.50 )

    VAR PRO =  IF(SELECTEDVALUE('Table'[License]) = "Power BI Pro" , Total * 13.10)

    VAR PFL =  IF(SELECTEDVALUE('Table'[License]) = "Free Users" , Total * 13.10 )

    VAR Result = SWITCH( TRUE() ,

        SELECTEDVALUE('Table'[License]) = "PPU" , Total * 22.5 ,

        SELECTEDVALUE('Table'[License]) = "PRO" , Total * 13.1 ,

        SELECTEDVALUE('Table'[License]) = "Free" , Total * 13.1 )

    RETURN

    IF( ISINSCOPE('Table'[License]) , Result , (PPU + PRO + PFL))

Data is very simple.

[v-sgandrathi]

Hi @Suhel_Ansari,

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

If you need any further assistance, feel free to reach out.

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[v-sgandrathi]

Hi @Suhel_Ansari,

Thank you @Royel @GeraldGEmerick @cengizhanarslan and @jgeddes for ypur response to the query.

Just wanted to follow up and confirm that everything has been going well on this. Please let me know if there’s anything from our end.
Please feel free to reach out Microsoft fabric community forum.

[cengizhanarslan]

Please try the following formula:

License Cost :=
VAR Lic   = SELECTEDVALUE ( 'Table'[License] )
VAR Users = DISTINCTCOUNT ( 'Table'[EmailID] )
VAR Price =
    SWITCH (
        Lic,
        "PBI_PREMIUM_PER_USER", 22.5,
        "Power BI Pro",         13.1,
        "Free Users",           0,
        0
    )
RETURN
IF (
    ISINSCOPE ( 'Table'[License] ),
    Users * Price,
    SUMX ( VALUES ( 'Table'[License] ), CALCULATE ( Users * Price ) )
)

[Suhel_Ansari]

hi @cengizhanarslan  - this meaure returns 0 as the output as seen in the following screen print. Thanks 

[cengizhanarslan]

Seems like License values are not same as in the field useds in the visual and use use DISTINCTCOUNT insted COUNTA. If you used COUNTA to avoid nulls use DISTINCTCOUNTNOBLANK instead.

[Royel]

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]

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

[Royel]

Ahh, in this case we need some work around, are you able to share some sample file with expected results. so that we can test and come with proper calculations for measure. 

[jgeddes]

Something like this approach may also work for you.

Measure = 
var _vTable = 
SUMMARIZE(
    'Table',
    'Table'[License],
    "__count", COUNT('Table'[License])
)
var _multiplier = 
SWITCH(
    SELECTEDVALUE('Table'[License]),
    "PPU", 22.5,
    "Pro", 13.1,
    "Free", 13.1,
    BLANK()
)
RETURN
SUMX(
    _vTable,
    [__count] * _multiplier
)

This creates a virtual table that summarzes the intial table by licence with the count of each license. The SWITCH _multiplier variable provides the correct multiplier for each licence type. 
The return statement sums the lines of the virtual table, multiplying the licence counts by the correct amount. 

[Suhel_Ansari]

Hi @jgeddes - The measure retunrs total a blank. Thanks 

[GeraldGEmerick]

@Suhel_Ansari  So, when multiple rows are in scope like in the total row, the SELECTEDVALUE is going to return BLANK. You will need to use ADDCOLUMNS to add your calculation to a table variable and then use SUMX across that table for the total row.

[Suhel_Ansari · Accepted solution]

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 

[GeraldGEmerick]

@Suhel_Ansari From what I can see, you can move the formulat for TOTAL into your Result variable. That way it isn't calculated unless it is necessary.